Selecting cell in filtered list with a Macro

Hi Rick,

 

Not sure if this is entirely what you want but feel free to get back to me if not.

The following example assigns the visible data to a range variable and then copies the visible data and pastes it to another worksheet.

Sub CopyFilteredData()

   

    Dim rngFilt As Range

   

    With Sheets("Sheet1").AutoFilter.Range

        Set rngFilt = .Offset(1, 0) _

          .Resize(.Rows.Count - 1) _

          .Cells.SpecialCells(xlCellTypeVisible)

    End With

   

    rngFilt.Copy Destination:=Sheets("Sheet2").Range("A1")

End Sub

 

Explanation:

A space and an underscore at the end of a line is a line break in an otherwise single line of code.

Sheets("Sheet1").AutoFilter.Range  is the entire AutoFiltered range (Includes both visible and non visible rows)

 .Offset(1, 0)   moves down one row off the column headers but now includes an extra row at the bottom. (Still includes visible and non visible.) 

 .Resize(.Rows.Count - 1)    removes extra row off bottom created by Offset. (Still includes visible and non visible)

 .Cells.SpecialCells(xlCellTypeVisible)     restricts to visible cells only.

Note: You cannot iterate through rows in filtered data because it will stop at the first non contiguous row. If you need to do this then please get back to me with an explanation of what you want to do because there is a work around.

Following added with Edit:

Not really sure what you mean by "Once the list has been filtered I want to select the resulting range". I thought you meant the entire filtered visible cells. Further down in your post you seem to be referring to an individual cell which is the first cell of the visible data.

You could replace the Copy/Destination line with the following line to select all of the visible data.

 

rngFilt.Select

 

Or you could use the following to select just the first cell of the visible data.

 

rngFilt.Cells(1, 1).Select

 

Over to you Rick and like I said before, fell free to get back to me.

Thanks this was great. I just had to modify it slightly as I didn't want all the columns, so once I added this to the Resize, it worked perfectly.

Hello Rick

I'm not sure if you are filtering only to set a reference to the first found cell.  If you are then I would suggest an alternative approach; either the Match (as you mentioned before), or use the Find Method.

Demo using the Match method:

Public Sub Demo_MatchMethod()

Dim varMatchRow As Variant

Dim rngFirstMatch As Range

With Sheet1

.AutoFilterMode = False

varMatchRow = Application.Match("b", .Range("A1:A15"), 0)

If IsNumeric(varMatchRow) Then

Set rngFirstMatch = .Cells(varMatchRow, "A")

End If

End With

End Sub

Demo using the Find method:

Public Sub Demo_FindMethod()

Dim rngFirstMatch As Range

On Error Resume Next

Set rngFirstMatch = Sheet1.Range("A1:A15").Find(What:="b", _

LookIn:=xlValues, _

LookAt:=xlWhole, _

SearchOrder:=xlNext, _

MatchCase:=False)

On Error GoTo 0

End Sub

Note, the find method does support case-sensitivity and also full and partial matches.

More help on the find method here:

http://msdn.microsoft.com/en-us/library/ff839746.aspx

Hope this helps,

Jon von der Heyden