VBA Excel Delete rows based on values in column and move on if it's empty

Question

I am very new with VBA and I am working with a table Tabel1 on my worksheet which has a varying range of columns and rows. I need to remove rows based on a value in the column Af. The cells in the column have a value or are empty. I thought I had it worked out thanks to many of you on this website, however, if the entire column is empty I get an error in the last section of the code myRange.Select. I get where it comes from, there is nothing to select. But I don't know how to skip it without getting more errors. I have searched this website, but can't seem to find anything that works. Below is the code that works fine when there're values in the column.

Dim tbl As ListObject
Dim x As Long
Dim myRange As Range

Set tbl = ActiveSheet.ListObjects("Tabel1")
    For x = 1 To tbl.Range.Rows.Count
        If tbl.DataBodyRange(x, Range("Tabel1[Af]").Column) >= 1 Then
            If myRange Is Nothing Then
                Set myRange = tbl.ListRows(x).Range
        Else
            Set myRange = Union(myRange, tbl.ListRows(x).Range)
        End If
    End If
Next x
myRange.Select
    Selection.Delete shift:=xlUp

Can anyone please help me skip past the .select part?

user4039065 · Accepted Answer · 2018-08-01 10:21:54Z

1

Change,

myRange.Select
    Selection.Delete shift:=xlUp

... to,

If not myRange Is Nothing Then _
    myRange.Delete shift:=xlUp

answered Aug 1, 2018 at 10:21

user4039065

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

1 Comment

Jalala.H13 Over a year ago

Thanks so much! I had been struggling with the If not ... Is Nothing Then but couldn't get it right!

Collectives™ on Stack Overflow

VBA Excel Delete rows based on values in column and move on if it's empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related