Reduce selection by one row

[awsumchillicrab]

Hi,

After I autofilter in VBA, I want to select the visible rows, but not the header row (row 1). Since the visible cells may not start with Row 2, I have resorted to this formula:

Range("A1").CurrentRegion.EntireRow.Offset(1,0).SpecialCells(xlCellTypeVisible).Delete

However, if my last row is Row 100, this also selects Row 101. How do I avoid this?

 

[Andrew Poulsom]

Like this?

With Range("A1").CurrentRegion.EntireRow
    .Offset(1,0).Resize(.Rows.Count -1).SpecialCells(xlCellTypeVisible).Delete
End With

 

[VoG]

Possibly

Range("A1").CurrentRegion.Offset(1).Resize(.CurrentRegion.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[awsumchillicrab]

Thanks, Andrew's code works.

Seems like we have no choice but to use a With structure. The code doesn't compile without it. Why not?

 

[VoG]

Maybe this

Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[Andrew Poulsom]

Useing With just avoids repetition. This compiles OK:

Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

 

[awsumchillicrab]

Oh I see...

Thanks!