Hopefully simple question

[entrekin]

I am writing a macro that I want to use to select all cells in filtered columns A:P except for the header row, which is always row 1. I've tried using

ActiveCell.SpecialCells(xlLastCell).Select
Range(Selection, Cells(1)).Select

but it selects all the way to A1. What I want is to select from the end to the first visible (based on the filter) cell below A1. I have a feeling the answer is simple, but I can't seem to figure it out. Actually, I thought my code worked the first time I ran it, but every time since then, it has selected all the way to A1. Any ideas?

 

[shg]

    With ActiveSheet.AutoFilter.Range
        .Offset(1).Resize(.Rows.Count - 1).Select
    End With

 

[Smitty]

Here's one way. Just note that it's generally not necessary to select.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    lr = Cells(Rows.Count, "A").End(xlUp).Row<br>    <br>    Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[entrekin]

I new it was simple. Thanks to both of you!!

 

[shg]

You're welcome.