CODEWITHSUNDEEP
excelvbaloops
Analyst1
Analyst1

Reputation: 1

VBA- Auto-delete empty rows with Looping Range

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!

Upvotes: 0

Views: 3011

Answers (2)

roses56
roses56

Reputation: 130

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

Upvotes: 0

Warcupine
Warcupine

Reputation: 4640

  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

Upvotes: 1

Related Questions