Tip Printed from ExcelTip.com
Copy all rows of a column without a value using VBA in Microsoft Excel


Question:
All rows, which do not contain a value in column G are to be copied to the second table.


Answer:
Insert the following code in the standard module.











Sub CopyEmptys()

   Dim intRow As Integer, intRowL As Integer, intRowT As Integer

   intRowL = Cells(Rows.Count, 2).End(xlUp).Row

   For intRow = 2 To intRowL

      If IsEmpty(Cells(intRow, 7)) Then

         With Worksheets(3)

            intRowT = .Cells(.Rows.Count, 2).End(xlUp).Row + 1

            .Range(.Cells(intRowT, 2), .Cells(intRowT, 7)).Value = _

               Range(Cells(intRow, 2), Cells(intRow, 7)).Value

         End With

      End If

   Next intRow

End Sub