AutoFilter issue 2

[TeddB]

After using an "Autofilter" to identify a group of needed rows, I want to create a Range containing only the visible rows. I have tried the following code, but receive and error in the last statement:

Range("A1&quot.Activate
var_StartDate = "11-1-00"
var_EndDate = "11-30-00"
Selection.AutoFilter Field:=4, Criteria1:=">=" _
& var_StartDate, Operator:=xlAnd _
, Criteria2:=&quot;<=&quot; & var_EndDate
Range(xlCellTypeVisible).Select


Looking for help,
Ted

 

[ilses]

Ted,

It should be possible, toying with the recorder turned up something like:

Dim myRange As Range
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="rood"
    Set myRange = Selection.CurrentRegion.SpecialCells(xlCellTypeVisible)
    myRange.Copy
    Range("G13").Select
    ActiveSheet.Paste
End Sub

Ilses

 

[TeddB]

Ilses,

Thank you for your suggested solution, however it does not give me what I am looking for.

Your suggestion gives me the ability to copy the visible rows, which is good and I can use this method as a work-around. However, this is not my need.

Perhaps the following example will help.

After executing the AutoFilter statement, I can now see rows 1,15,17,18,25,26,27,28,29,50.

Row 1 is a header row.

Now, I want to know the value of the cell in the first row, first column following row 1, which is row 15.

If I execute statements such as; Range(&quot;A2&quot.Select, or use and Offset(1,0) approach I am given values which exist in the hidden row 2 instead.

I hope this makes my objective clearer.

Thanks,
Ted

 

[ilses]

Hi Ted,

To be able to address the visible rows, the following sample puts the number of all visible rows in an array. The message box shows the value of the first visible row excluding the header row - row 1 in your sheet, which is aVisibleRows(0).

The message box is there just because I do no know how you want to fix the row or column to be shown or used elsewhere in your code.

Dim myRange As Range
Dim r As Range
Dim aVisibleRows() As Long
Dim i As Long

Range("a1").AutoFilter Field:=1, Criteria1:="red"
Set myRange = Range("a1").CurrentRegion

For Each r In myRange.Rows
    If Not r.Hidden Then
        ReDim Preserve aVisibleRows(i)
        aVisibleRows(i) = r.Row
        i = i + 1
    End If
Next r

MsgBox Cells(aVisibleRows(1), 1).Value

End Sub

ilses

 

[TeddB]

Ilses,

After hours of head-scratching and keyboard tapping, I have what I was looking for.

You put me on base with your first suggestion with the line:
Set myRange = Selection.CurrentRegion.SpecialCells(xlCellTypeVisible)

However, when I tried to reference cell &quot;A1&quot; (expecting to have the value of the first row from the now filtered range), I was given a value for the first row in the unfilitered table instead.

One minor change did the trick- adding an &quot;OFFSET&quot;

The new line now reads:
Set myRange = Selection.CurrentRegion.Offset(1,0).SpecialCells(xlCellTypeVisible)

I am now able to address cells relative to the filtered rows only.

Oh! One other thing. Trying to select the cell as:
myRange(&quot;A1&quot didn't work.

It is necessary to address the cell as:
myRange(&quot;1&quot;,&quot;A&quot

I have a dream...
To one day understand the vague nuances of Excel VBA X-)

Some hair remaining,
Ted

 

[ilses]

Ted,

Range(&quot;A&quot;, &quot;1&quot - cool!

ilses

 

[TeddB]

Ilses,

I tried Range(&quot;A&quot;,&quot;1&quot first and received the error &quot;Type Mismatch&quot;.

Had to change reference to Range(&quot;1&quot;,&quot;A&quot for it to work.

Crazy, huh s-)

Ted

 

[ilses]

Must be mixing things up, even the star that I wanted to attach to your post didn't show up. Had to try again.

ilses