Select first row of data from autofilter

[mcongdon]

I have an autofilter set up through VBA in an Excel 2007 worksheet. Once it filters, I want to delete all the values it returns and remove the autofilter. The problem I'm having is how to select the first row of the filtered data- sometimes it's row 2, sometimes it's row 456. How do I select ONLY the data returned by the autofilter?

 

[PHV]

Have a look at SpecialCells(xlCellTypeVisible)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Do you want the FIRST visible row or ALL visible rows?

Check out the specialcells(xlcelltypevisible) peoperty of the range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcongdon]

That's definitely what I was looking for, now is there a way to NOT select the headers and cells below the filtered selection? Here's what my code looks like now- it might be rough, but hey, I'm an amateur

Sub Step2()
' Filter out xxx and yyy account numbers
    Sheets("Insert Raw Data Here").Select
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$X$50000").AutoFilter Field:=1, Criteria1:="=xxx*", _
        Operator:=xlOr, Criteria2:="=yyy*"
    ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$X$50000").AutoFilter Field:=1
End Sub

The 50000 was just a number that I pulled out of thin air because there will never be more than that many rows. Thanks!

 

[PHV]

What about this ?
Application.Intersect(ActiveSheet.Cells.SpecialCells(xlCellTypeVisible), ActiveSheet.Range("$A$[!]2[/!]:$X$50000")).Delete Shift:=xlUp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mcongdon]

That still deleted the column headers, but it didn't delete any of the data I need. I may just add the correct column headers to a new sheet in the workbook and insert a row after this procedure. Here's what my code would be (unless we can think of a way not do delete the headers):

Sub Step2()
' Step2 Macro
' Filter out xxx and yyy account numbers
    Sheets("Insert Raw Data Here").Select
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$X$50000").AutoFilter Field:=1, Criteria1:="=xxx*", _
        Operator:=xlOr, Criteria2:="=yyy*"
    Application.Intersect(ActiveSheet.Cells.SpecialCells(xlCellTypeVisible), ActiveSheet.Range("$A$2:$X$50000")).Delete Shift:=xlUp
    Cells.Select
    Selection.AutoFilter
    Sheets("Reference Sheet").Select
    Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Insert Raw Data Here").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
End Sub

 

[mcongdon]

Never mind- PHV's snippet worked beautifully. For some reason when I ran it step wise in the debugger it didn't work, but when I ran the whole procedure through a command button it worked perfectly. Thanks for all the help guys, it's really appreciated. I'll definitely keep that SpecialCells() method in mind in the future!

 

[AnotherHiggins]

There's actually no need to even bother specifying visible cells only. Once you have the autofilter in place, just delete from Row 2 to the end of the sheet. Excel defaults to only deleting visible cells.

    dblAvailRows = ActiveSheet.Rows.Count

    ActiveSheet.AutoFilterMode = False
    Cells.AutoFilter FIELD:=1, Criteria1:="A", Operator:=xlOr, Criteria2:="B"
    Range("2:" & dblAvailRows).Delete
    ActiveSheet.AutoFilterMode = False

(I use dblAvailRows because we have users on multiple Office versions so the max number of rows could be 65536 or 1048576)

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mcongdon]

That's good to know and keep in mind as well, thanks!