Excel VBA - Scan range and delete empty rows [duplicate]

Question

I have a spreadsheet that populates rows based on data from a pivot table (imported through ODBC). I'm using VLOOKUP, for example:

=VLOOKUP(A8;Data!B1:I298;2;FALSE)

The result is something like

Name1
Name2
Address1
Address2
Postalcode
Country

It might happen that some of the pivot columns are empty, resulting in

Name1
0
Address1
0
Postalcode
0

What I need is some sort of function that loops through a range of rows, for example A8 - A14 and delete the rows that are "empty". My problem is that the rows are not truly empty, they still return 0 and contain the VLOOKUP formula.

Is this achievable somehow? I hope I'm making sense.

Thanks.

you could apply filter searching for '0' and 'empty' cells. Next you could delete entire rows. Try with macrorecorder- possibly this will be enough for you. — Kazimierz Jawor
– Kazimierz Jawor, Commented Oct 23, 2013 at 9:24

user2140173 · Accepted Answer · 2013-10-23 09:33:11Z

3

Example

enter image description here

with the code

Sub Delete0s()
    Dim i As Long
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("A" & i) = 0 Then
            Range("A" & i).Delete shift:=xlUp
        End If
    Next i
End Sub

deletes 0s so the result

enter image description here

achieve the same result using autofilter which is normally a bit faster than looping

enter image description here

code

Sub Delete0s()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:A" & lastRow)
    With rng
        .AutoFilter Field:=1, Criteria1:="=0"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ws.AutoFilterMode = False
End Sub

result

enter image description here

answered Oct 23, 2013 at 9:33

user2140173

Sign up to request clarification or add additional context in comments.

1 Comment

Morten Laustsen Over a year ago

This solved the issue. I ended up using the autofilter option. Thank you for the thorough explanation.

Collectives™ on Stack Overflow

Excel VBA - Scan range and delete empty rows [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related