Delete Visible Rows in a sheet that has hidden columns

user_19035464 0
Viewed 31

I have this code that works fine on sheets that have no hidden columns, whereas this sheet does have hidden columns. I want to delete all visible rows, including the information that is in the hidden columns.

TempBook.Sheets("Item Level Detail").Activate

Set rng2 = Range("A1:AO500000")
    rng2.AutoFilter Field:=1, Criteria1:="<>" & DistNo, Operator:=xlAnd

ActiveSheet.Range("A2:AO500000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
TempBook.Sheets("Item Level Detail").AutoFilter.ShowAllData

I get the error "cannot use that command on overlapping selections" because all visible rows have hidden columns.

The visible rows change frequently throughout this code, this is one step within the loop I have.

How can I delete all visible rows without getting the error and without hard-coding in which rows to delete?

vba excel
1 Answers

The hidden columns are causing SpecialCells to return the cells in separated areas. For example if column D was hidden, Column A:C and columns E:AO would be two areas. Then when using EntireRow, each area is expanding into the same rows. For example, A1 and E1 are both becoming 1:1. This is why you're having the overlap error.

To avoid the overlap from other sections, limit the search for visible cells to a single column like "A2:A500000" instead of "A2:AO500000".

user_146087500
Related