VBA Loop combining Lastrow and finding blank values

excel vba find last row in column with data
excel vba find next empty row in range
excel vba find first empty cell in row
excel vba loop through rows until empty row
excel vba loop until end of data
excel vba loop through rows find value
excel vba find last row with data in range
vba select last cell in column

I am trying to build a "data check" type file where a series of macros look at a data set and copy/paste incorrect entries into separate sheets based on various criteria. One of these is looking at if the value in Column A is blank.

Below is the code that I currently have. It only takes the first instance of the blank, and I am trying have it loop to find all the blank values in column A.

Sub copy_blanks()  
    Dim sr As Range
    Dim blank As Long
    Dim i As Integer
    Dim s1 As Worksheet
    Dim s2 As Worksheet

    Set s1 = Worksheets("data")
    Set s2 = Worksheets("No LoadID")

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

    Set sr = Worksheets("data").Range("A:A").Find("")

    If Not sr Is Nothing Then  
        blank = sr.Row
        s1.Rows(blank).Copy
        s2.Cells(lr2 + 1, 1).PasteSpecial xlPasteValues
    End If
End Sub

Have a look at the Range.SpecialCells Method. You can use SpecialCells(xlCellTypeBlanks) to find all blank cells in a range.

Dim wsData As Worksheet
Set wsData = Worksheets("data")

Dim LastDataRow As Range
Set LastDataRow = wsData.Cells(Rows.Count, "A").End(xlUp) 'last used cell in column A

Dim wsNoID As Worksheet
Set wsNoID = Worksheets("No LoadID")

Dim BlankCells As Range
On Error Resume Next 'next line will throw an error if no blanks are found
Set BlankCells = wsData.Range("A1", LastDataRow).SpecialCells(xlCellTypeBlanks) 'find all blank cells in column A until last data row
On Error Goto 0 're-activate error messages!

If Not BlankCells Is Nothing Then
    BlankCells.EntireRow.Copy

    wsNoID.Cells(Rows.Count, 2).End(xlUp).Offset(1, -1).PasteSpecial xlPasteValues
Else
    MsgBox "No blanks found."
End If

VBA Loop combining Lastrow and finding blank values, SpecialCells(xlCellTypeBlanks) 'find all blank cells in column A until last data row On Error Goto 0 're-activate error messages! If Not BlankCells Is Nothing Then  One to find the last row and one to find the last column. You can then combine these to reference the last cell. Here are the help articles for Range.End. MSDN help page for Range.End; MSDN help for xlDirection Enumerations #2 – The Range.Find() Method. The Range.Find method is my preferred way to find the last row, column, or cell. It is the

I prefer using autofilter for this sort of work since that will capture cells that are blank as the result of formulas (and thus contain "" so they look blank) as blank as well as actually blank cells. Code assumes headers are row 1 and actual data starts in row 2:

Sub copy_blanks()

    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim lr2 As Long

    Set s1 = ActiveWorkbook.Worksheets("data")
    Set s2 = ActiveWorkbook.Worksheets("No LoadID")

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

    With s1.Range("A1:A" & s1.Cells(s1.Rows.Count, "B").End(xlUp).Row)
        .AutoFilter 1, "="
        .Offset(1).EntireRow.Copy
        s2.Cells(lr2 + 1, "A").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        .AutoFilter
    End With

End Sub

How to loop through rows until blank in Excel column?, Loop through rows until blank with VBA. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window. Click Insert > Module, and paste below code to the blank script. VBA: Loop until blank. Sub Test1() Press F5 key to begin looping the column, then the cursor will stop at the first met blank cell. Last Row in a Column. To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.. Dim lastRow as Range 'Get Last Row with Data in Column Debug.Print Range("A1").End(xlDown).Row 'Result: 5 Set lastRow = Range("A1").End(xlDown).EntireRow 'Get Last Cell with Data in Row Dim lastRow as Range Set lastRow = Range("A1").End(xlDown)

You question implies you will have more criteria to search for so I decided to keep the loop. You can add more criteria here as you go ~

  1. Loop through Column A
  2. If value is blank add the cell to a Union (collection of cells)
  3. Once loop is complete, copy the Union all at once

This can be improved upon by switching from a For i loop to a For Each loop to go through a range. Another way to do this is simply filter Column A by blanks and copy/paste the visible rows that remain.

Option Explicit

Sub Blanks()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("data")
Dim db As Worksheet: Set db = ThisWorkbook.Sheets("No LoadID")

Dim LROw As Long, i As Long, Blanks As Range

For i = 2 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    If ws.Range("A" & i) = "" Then
        If Not Blanks Is Nothing Then
            Set Blanks = Union(Blanks, ws.Range("A" & i))
        Else
            Set Blanks = ws.Range("A" & i)
        End If
    End If
Next i

If Not Blanks Is Nothing Then
    Blanks.EntireRow.Copy db.Range("B" & db.Rows.Count).End(xlUp).Offset(1, -1).Row
End If

End Sub

Find and Select the First Blank Cell in a Column VBA, Find and Select the First Blank Cell in a Column VBA. ExcelHowTo Mar Row '​Step 3: Select the next row down Cells(LastRow, 1).Offset(1, 0). Loop through rows until blank with VBA. 1. Press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window. 2. Click Insert > Module, and paste below code to the blank script. VBA: Loop until blank

VBA Tutorial: Find the Last Row, Column, or Cell in Excel, Intermediate. VBA Methods to Find the Last Cell Row Column in Excel Row '​Find the Duration: 6:56 Posted: May 11, 2015 If value is blank add the cell to a Union (collection of cells) Once loop is complete, copy the Union all at once This can be improved upon by switching from a For i loop to a For Each loop to go through a range.

Excel VBA : Find Last Used Row and Last Used Column, It ignores cells containing formulas displaying the empty string i.e. ="". Sub lastusedrow1() Dim last As Long last = Worksheets("Sheet1").Cells.Find(What:="*​",  Let me start by saying I don't know VBA. I'm trying to work with 11,000 lines of products that I'm trying to upload to an ecommerce store. To fill in the Tags column without doing each line individually, I've recorded a macro to find a word, tab over 5 columns and type that word, and then tab again and type ", word".

Excel VBA Delete Blank Or Empty Rows: 5 Easy-To-Use Macro , How to delete empty rows or rows with blank cells in Excel. The last 2 macros delete rows when the entire row is empty. How would you modify the VBA code in order to avoid using the Select method and Selection property? Next statement, which is a common way of structuring a loop in Visual Basic for Applications. Find and Select the First Blank Cell in Column A Sub Macro1() Dim ws As Worksheet Set ws = ActiveSheet For Each cell In ws.Columns(1).Cells If IsEmpty(cell) = True Then cell.Select: Exit For Next cell End Sub

Comments
  • Do you want the whole row to be copied when you have a blank?
  • @urdearboy Yes I would like to bring over the whole row, is this still satisfied with the below?
  • Yup. Looks like all 3 solutions are grabbing the entire row
  • Thank you to everyone for your help! All the answers proved to be useful and I will be able to integrate methods from each into the remainder of the file. Thanks!
  • Was going to answer with the SpecialCells method - would need to add an error handler in case there's no blank cells.
  • @DarrenBartrup-Cook Correct. Thank you for pointing out. Fixed it.
  • Sorry to stick my oar in again - Set BlankCells = wsData.Range("A1", LastDataRow).SpecialCells(xlCellTypeBlanks) would throw the error. Could be a rare case of On Error Resume Next.
  • @DarrenBartrup-Cook My bad again :( I'm getting a coffee now. Thanks again!
  • db.Range("A" & db.Rows.Count).End(xlUp).Offset(1) must be db.Range("B" & db.Rows.Count).End(xlUp).Offset(1, -1) because there is no data in column A if you copy the rows (only having a blank in A).