VBA: Deleting rows that have a blank in a certain column and making a button in Excel

0
votes

So I am trying to write VBA code to delete an entire row in Excel if a cell in that row in a certain column is blank. I am completely new to VBA, so I literally just had to learn as I coded. I think I have the methodology down, but it always leaves one last row with a blank in that specific column in the worksheet. In some cases, it leaves 5 rows with a blank in that specific column. Any help fixing would be appreciated, thank you!

I also want to have it where I can click on a button in the workbook, more than likely on a separate sheet, and it will do the deleting rows methodology for a specific sheet. If that is possible, am I able to move all of that methodology from one from workbook to another?

I've already tried some ways of implementing both of these using VBA, but this is my first time ever using VBA, so a lot is still new to me.

Private Sub Button_Click()
Dim LR As Long
Application.ScreenUpdating = False
    For LR = Sheets("Test2").Range("AB" & Rows.Count).End(xlUp).Row To 2 Step 1
        If Range("AB" & LR).Value = "" Then
            Rows(LR).EntireRow.Delete
        End If
    Next LR
Application.ScreenUpdating = True
End Sub

I expect all the rows with blanks in that column to be deleted when I press the button in the worksheet.

1
excelvba
If you want to delete rows use step -1 - Luuklag

1 Answers

0
votes

If the blank cells in that column are truly blank and not zero length strings returned by formulas (e.g. "") then SpecialCells can remove them all at once.

Private Sub Button_Click()

    Intersect(Me.Columns("AB"), Me.Cells.SpecialCells(xlCellTypeBlanks)).EntireRow.Delete

End Sub

For your own code, you should step backwards in your loop (Step -1). Further, you should not look at column AB for the last cell. If there were blanks in the last row or rows, they would be ignored.