'Copy Pasting Data from a column to a free column, removing empty lines

I was not looking at VBA for a while, and I would really appreciate your help.

Private Sub EvaluateButton_Click()
Dim r As Long
Dim x As Long

Worksheets("testsheet").Activate

r = Worksheets("testsheet").("C" & Rows.Count)
  1. I would like to get back the number of the last row as variable r
Worksheets("testsheet").Range("C10:C999").Copy
  1. I would like to then use r to define how long my range is that I copy
Worksheets("testsheet").Range("Z15").Select
  1. Similarly, I would like to check the first free column starting at z, 'so that any new instance of "EvaluateButton_Click()" will move a column to the right 'and not overwrite the data pasted previously. 'so that my range is ("Z" + x) 'starting with x = 0, i guess i would have to set x publicly to store it 'perhaps print the value in a cell and read it in again.
Worksheets("testsheet").Range("Z15").Select
  1. I have empty cells between the data: 'I would like to eliminate it in the pasted line. 'select non-empty cells For Each self In Selection.SpecialCells(xCellTypeConstants) Selection.SpecialCells(xCellTypeConstant).Select 'i want the opposite, non-constants Selection.Delete 'Debug.Print (self) 'Next 'deleting empty cells in this range, i need to "move up" the remaining data entries. 'I do not know how that is being done.1
cssexcelvba


Solution 1:[1]

My function now does just that, to copy data in a new row without empty lines, and some additional information for analysing this data.

Function

Private Sub EvaluateButton_Click()
    Dim EvaRange As Range               'Evaluation range
    Dim i As Integer                    'Counter for free columns
    Dim n As Integer                    'Counter for number of measurements
    Dim a As Integer                    'Counter for individual request
    Dim AbfrageZahl As Variant          'Counter for reuquest cycle number
    Dim z As Integer                    'counter user input column definition print
    
    Worksheets("AC Noise").Activate     'choose the sheet
    'MsgBox "The name of the active sheet is " & ActiveSheet.Name
    z = 0
    AbfrageZahl = InputBox("Bitte geben Sie eine Zahl >1 ein für die Abfrage. Q2 2022: 2, Q4 2022: 3, Q2 2023: 3 usw.")
    z = AbfrageZahl - 2
    Set EvaRange = Range("C10:C999")
    EvaRange.Copy Cells(15, 7 + 2 * z)
    Range(Cells(15, 7 + 2 * z), (Cells(Rows.Count, 7 + 2 * z))).Select
        i = 15
        For Each self In Selection.SpecialCells(xlCellTypeConstants)
            self.Copy Cells(i, 8 + 2 * z)
            i = i + 1
        Next
    Cells(i - 1, 8 + 2 * z).Clear        'und jetzt die Auswertung
    n = i - 16                           'definiere mein n
    Cells(1, 8 + 2 * z).Value = Date     'schreibe Abfragedatum
    Cells(2, 8 + 2 * z).Value = n        'schreibe mein n
    Cells(3, 8 + 2 * z).Value = Application.WorksheetFunction.Average(Range(Cells(15, 8 + 2 * z), Cells(Rows.Count, 8 + 2 * z)))
    Cells(4, 8 + 2 * z).Value = Application.WorksheetFunction.StDev_S(Range(Cells(15, 8 + 2 * z), Cells(Rows.Count, 8 + 2 * z)))
    Cells(5, 8 + 2 * z).Value = Range("C1").Value
    Cells(6, 8 + 2 * z).Value = Range("C2").Value
    Cells(7, 8 + 2 * z).Value = Range("C3").Value
    Cells(8, 8 + 2 * z).Value = Range("C4").Value
    Cells(9, 8 + 2 * z).Value = Range("C5").Value
    Cells(10, 8 + 2 * z).Value = Range("C6").Value
    Cells(11, 8 + 2 * z).Value = ActiveSheet.Name
    Cells(12, 8 + 2 * z).Value = AbfrageZahl
    Cells(1, 7 + 2 * z).Value = "Date of Review"
    Cells(2, 7 + 2 * z).Value = "n, Number of Measurements"
    Cells(3, 7 + 2 * z).Value = "µ, arithmetic mean"
    Cells(4, 7 + 2 * z).Value = "Sigma, stand. Dev. of a batch"
    Cells(5, 7 + 2 * z).Value = Range("B1").Value
    Cells(6, 7 + 2 * z).Value = Range("B2").Value
    Cells(7, 7 + 2 * z).Value = Range("B3").Value
    Cells(8, 7 + 2 * z).Value = Range("B4").Value
    Cells(9, 7 + 2 * z).Value = Range("B5").Value
    Cells(10, 7 + 2 * z).Value = Range("B6").Value
    Cells(11, 7 + 2 * z).Value = "Test Name"
    Cells(12, 7 + 2 * z).Value = "Review Cycle Nr."
End Sub

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hannes Ulbricht

Related Questions