How to Copy Non-Blank rows in a Range and Paste to Other Sheets

How to copy non-blank rows in a range and paste to other sheets

Requirement:

The user has this code that copy range and paste to other sheets:

Code:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet


    Set copySheet = Worksheets("Data")
    Set pasteSheet = Worksheets("Report")


    copySheet.Range("A105:F110").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
End Sub

However, when the copy range has only values in range A105:F105 and range below are blanks, it copies all range to worksheet("Report"). And when i add another data, it started in the row where the upper row is empty.

What is the missing code that only copy range with values and paste it to worksheet("Report")? So that when the user adds another data, it will paste next to it...

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/149020-copy-non-blank-rows-in-a-range-and-paste-to-other-sheets

Solution:

Code:

Sub CopyRange()
    Dim x, y(), i As Long, ii As Long
    
    x = Sheets("Data").[a105:f110]
    For i = 1 To UBound(x, 1)
        If x(i, 1) <> "" Then
            ReDim Preserve y(1 To 6, 1 To i)
            For ii = 1 To 6
                y(ii, i) = x(i, ii)
            Next
        Else: Exit For
        End If
    Next
    With Sheets("Report")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(UBound(y, 2), 6) = Application.Transpose(y)
    End With
    
End Sub

Solution from KjBox.

my loop less alternative

Code:

Option Explicit
Sub Test()
    With Sheets("Data").Range("A104:F110")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>"
        .Offset(1, 0).Copy
    End With
    With Sheets("Report")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                                                            :=False, Transpose:=False
    End With
    With Sheets("Data").Range("A104:F110")
        .AutoFilter
    End With
End Sub

Solution from pike.

Obtained from the OzGrid Help Forum.

Solution provided by KjBox and pike.