Copy Data from One Range to Another in Excel VBA disregarding empty cells

[Babynod]

Hi All,

i have a very simple copy paste code from an input sheet(DROP) to the main sheet (sheet1)

this works but as i am copying a range when i use this multiple times it inserts the data in the next row after the previous range was pasted in, not the next empty cell.
how do i make it disregard empy cells

Private Sub CommandButton1_Click()

    Range("K4:V33").Copy
    Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End Sub

there is the range it copies from (K4:V33). as the size of this list varies i need to copy the whole thing but exclude the empty rows

this is where it paste to

 

[LazyBug]

Private Sub CommandButton1_Click()
Dim r%, nr%, arr, lr&, i%
arr = Worksheets("DROP").Range("K4:V33")
    For r = 1 To UBound(arr)
        If arr(r, 1) <> "" Then
            nr = nr + 1
            For i = 1 To 12
                arr(nr, i) = arr(r, i)
            Next i
        End If
    Next r
    
    With Sheets("Sheet1")
        lr = .Range("B" & Rows.Count).End(xlUp).Row + 1
        .Range("B" & lr & ":" & "M" & lr).Resize(nr) = arr
    End With
End Sub

 

[Babynod]

Private Sub CommandButton1_Click()
Dim r%, nr%, arr, lr&, i%
arr = Worksheets("DROP").Range("K4:V33")
    For r = 1 To UBound(arr)
        If arr(r, 1) <> "" Then
            nr = nr + 1
            For i = 1 To 12
                arr(nr, i) = arr(r, i)
            Next i
        End If
    Next r
   
    With Sheets("Sheet1")
        lr = .Range("B" & Rows.Count).End(xlUp).Row + 1
        .Range("B" & lr & ":" & "M" & lr).Resize(nr) = arr
    End With
End Sub

Spot on mate thankyou very much.
are you able to break it down so i can understand how this works?

 

[Babynod]

Private Sub CommandButton1_Click()
Dim r%, nr%, arr, lr&, i%
arr = Worksheets("DROP").Range("K4:V33")
    For r = 1 To UBound(arr)
        If arr(r, 1) <> "" Then
            nr = nr + 1
            For i = 1 To 12
                arr(nr, i) = arr(r, i)
            Next i
        End If
    Next r
   
    With Sheets("Sheet1")
        lr = .Range("B" & Rows.Count).End(xlUp).Row + 1
        .Range("B" & lr & ":" & "M" & lr).Resize(nr) = arr
    End With
End Sub

So ive changed the ranges as ive changed the layout of my sheet, but now im getting an error

When i debug it it highlights arr(nr, i) = arr(r, i)

Private Sub CommandButton1_Click()
Dim r%, nr%, arr, lr&, i%
arr = Worksheets("DROP").Range("L4:T100")
    For r = 1 To UBound(arr)
        If arr(r, 1) <> "" Then
            nr = nr + 1
            For i = 1 To 12
                arr(nr, i) = arr(r, i)
            Next i
        End If
    Next r
    
    With Sheets("MASTER")
        lr = .Range("B" & Rows.Count).End(xlUp).Row + 1
        .Range("B" & lr & ":" & "J" & lr).Resize(nr) = arr
    End With
End Sub

For my layout now, i paste an extract in the left side, then i click my SPLIT macro button, this does TextToColumns on column H as the extract has Qty/Type/Supplier all in 1 column with / between them, so the TextToColumns separates by /.
This lines everything up on the right side (right side is just formulas to equal the corresponding cell on the left side e.g L4 =B4)

Also, where would i add in Range(B4:J100).clear so after the code runs and copys the right side onto my master sheet, it then clears the left side ready for the next extract to be dropped in?

 

[LazyBug]

So ive changed the ranges as ive changed the layout of my sheet, but now im getting an error

Respectively, here you need to specify the more specific address of the data you want to copy. L-T columns? (in the post #1 you wrote: " (K4:V33)")

Also, where would i add in Range(B4:J100).clear so after the code runs and copys the right side onto my master sheet, it then clears the left side ready for the next extract to be dropped in?

I suggest that we first solve the first stumbling block in the code, and then consider the other questions.