Excel VBA - skip blank entries

[shophoney]

Hi,

I have VBA that copies from one sheet to another (Register).

I would like the VBA to skip coping any entries that are blank.

Any lines where "H##" is blank, should be skipped. So if H20 has a value. Copy. If the next line H21 is blank then skip that line and don't run the nextrow.

Sub PostToRegisterDetailed()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("CC Form")
Set WS2 = Worksheets("CC Register Detailed")

'Figure out which row is the next row

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 8).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B20"), WS1.Range("F20"), WS1.Range("H20"), WS1.Range("J20"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B21"), WS1.Range("F21"), WS1.Range("H21"), WS1.Range("J21"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B22"), WS1.Range("F22"), WS1.Range("H22"), WS1.Range("J22"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B23"), WS1.Range("F23"), WS1.Range("H23"), WS1.Range("J23"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B24"), WS1.Range("F24"), WS1.Range("H24"), WS1.Range("J24"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B25"), WS1.Range("F25"), WS1.Range("H25"), WS1.Range("J25"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B26"), WS1.Range("F26"), WS1.Range("H26"), WS1.Range("J26"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B27"), WS1.Range("F27"), WS1.Range("H27"), WS1.Range("J27"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B28"), WS1.Range("F28"), WS1.Range("H28"), WS1.Range("J28"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B29"), WS1.Range("F29"), WS1.Range("H29"), WS1.Range("J29"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B30"), WS1.Range("F30"), WS1.Range("H30"), WS1.Range("J30"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B31"), WS1.Range("F31"), WS1.Range("H31"), WS1.Range("J31"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B32"), WS1.Range("F32"), WS1.Range("H32"), WS1.Range("J32"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B33"), WS1.Range("F33"), WS1.Range("H33"), WS1.Range("J33"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B34"), WS1.Range("F34"), WS1.Range("H34"), WS1.Range("J34"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B35"), WS1.Range("F35"), WS1.Range("H35"), WS1.Range("J35"))

nextrow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
WS2.Cells(nextrow, 1).Resize(1, 5).Value = Array(WS1.Range("A50"), WS1.Range("J50"), WS1.Range("C8"), WS1.Range("C4"), _
WS1.Range("B36"), WS1.Range("F36"), WS1.Range("H36"), WS1.Range("J36"))

End Sub

 

[DanteAmor]

Try this:

Sub PostToRegisterDetailed()
  Dim i As Long
  
  With Sheets("CC Form")
    For i = 20 To 36
      If .Range("H" & i).Value <> "" Then
        Sheets("CC Register Detailed").Range("A" & Rows.Count).End(3)(2).Resize(1, 8).Value = _
          Array(.[A50], .[J50], .[C8], .[C4], _
          .Range("B" & i), .Range("F" & i), .Range("H" & i), .Range("J" & i))
      End If
    Next
  End With
End Sub

 

[shophoney]

Try this:

Sub PostToRegisterDetailed()
  Dim i As Long
 
  With Sheets("CC Form")
    For i = 20 To 36
      If .Range("H" & i).Value <> "" Then
        Sheets("CC Register Detailed").Range("A" & Rows.Count).End(3)(2).Resize(1, 8).Value = _
          Array(.[A50], .[J50], .[C8], .[C4], _
          .Range("B" & i), .Range("F" & i), .Range("H" & i), .Range("J" & i))
      End If
    Next
  End With
End Sub

Thanks, I will give it a try.

It looks amazing, cutting the code down so much.

Thanks for supporting Excel!!!