0

First time using the site as I am new to VBA, but I am trying to write a piece of code that will look through a column of my choice, in this case column A, and go through each row and delete empty rows until the end of the dataset. I was thinking of doing a loop where I would reference the first cell in my dataset A1 and set the row number as a integer which would increase by 1 with each completion of the loop.

Private Sub CommandButton1_Click()

Dim X as Integer
    Set X = 1
For X = 1 to 100
If Sheet1.Range("A":X).Value = "" Then Rows(X).EntireRow.Delete
Next X

End Sub

Thanks for any help or insights you can provide!

Analyst1
  • 1
  • 1
  • I want to know what's wrong with my code. For A1:A100 I want it to autmatically go through and delete empty rows, but I'm having trouble. I think the simplest way to solve it would be to just loop and change the row reference in column A by 1 each time. Like A1 = 0 → delete row, A2 =1 → keep, A3 =8 → keep, A4 = 0 → delete. – Analyst1 Jul 21 '20 at 15:08
  • For what it is worth, it is by far simpler and much faster to filter on the column for empty cells, then use **.SpecialCells(xlCellTypeVisible)** to delete the visible rows. Refer to: https://stackoverflow.com/questions/17194394/vba-how-to-delete-filtered-rows-in-excel – XLmatters Jul 21 '20 at 15:20

2 Answers2

1

  1. You want to concatenate in Range("A":X) so change : to & (or use cells).

  2. When deleting rows you should step backwards or create a unionized range otherwise you will skip a row with every deletion you perform.

  3. You don't want to set integers that is only for objects. There is also no benefit from using integer over long in VBA so best to just always use long as integer can give overflow errors in very large spreadsheets.

  4. Rows(X).EntireRow.Delete is using a relative reference not an explicit one, use a with or explicitly reference every range object.

  5. You are immediately overwriting X with the loop so you don't need to assign it a value before the loop.

Here's some code that will do what you need:

    Dim lastrow As Long
    Dim x As Long
    
    With Sheet1
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For x = lastrow To 1 Step -1
            If .Cells(x, 1).Value = "" Then
                .Rows(x).EntireRow.Delete
            End If
        Next x
    End With
Warcupine
  • 4,460
  • 3
  • 15
  • 24
0

You are missing your "End if". Also when looping through a range and deleting rows you need to loop bottom up because when a row is deleted it does not recalculate the range.

Sub CommandButton1_Click()

Dim x As Long
Dim lastrow As Long
    lastrow = Range("A1" & Rows.Count).End(xlUp).Row

        For x = lastrow To 1 Step -1

            If Worksheets(1).Range("A" & x).Value = "" Then 
                Worksheets(1).Range("A" & x).EntireRow.Delete
            End If
        Next x

End Sub
roses56
  • 130
  • 9