How to make the first cell in autofiltered list the activecell?

[Guest]

Hi,

I have a list of data that I autofilter to show only one row. From this row
I want to assign data to variables with the attached code.

My problem is that the active cell is never in my autofiltered list and I do
not seem to be able to get it there. How do I get a fix reference (such as
the active cell) on the only visible row in a fixed column?

Please help,
Peter

Selection.AutoFilter Field:=3, Criteria1:=myReqRev

' How do I change the activecell to be on the only and/or first visible row?

With ActiveCell
myReqSourceID = .Offset(0, 0).Value
myReqText = .Offset(0, 1).Value
myValue = .Offset(0, 2).Value
myUnit = .Offset(0, 3).Value

 

[Guest]

Now I have tested a solution that works, but it is not a nice solution:

Improvement suggestions are still very welcome.

Peter


Selection.AutoFilter Field:=3, Criteria1:=myReqRev

Range("ReqID").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.EntireRow.Hidden = False

With ActiveCell
myReqSourceID = .Offset(0, 7).Value
myReqText = .Offset(0, 8).Value
myValue = .Offset(0, 9).Value

 

[Jim Cone]

If you are using a "later" version of Excel?...
(http://www.cpearson.com/excel/newposte.htm)

The following identifies the range that is visible on the screen.
(the non-filtered range)
It is shown in steps to make it a little clearer.

Sub FindTheVisibleRange()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
'Exclude header row
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
'Get the visible cells
Set rng = rng.SpecialCells(xlCellTypeVisible)
MsgBox rng.Address
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Peters"
<[email protected]>
wrote in message
Now I have tested a solution that works, but it is not a nice solution:
Improvement suggestions are still very welcome.
Peter
Selection.AutoFilter Field:=3, Criteria1:=myReqRev
Range("ReqID").Select
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.EntireRow.Hidden = False
With ActiveCell
myReqSourceID = .Offset(0, 7).Value
myReqText = .Offset(0, 8).Value
myValue = .Offset(0, 9).Value

 

[Guest]

Hello Jim,

Thanks for the advice. I have tried something similar but could not get it
working. I will try again with this version!

Regards,
Peter S