One more way:

Option Explicit
Sub RefreshModel()

Dim rng As Range

With ActiveSheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count 1 Then
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
Else
MsgBox "no Visible cells in filter!"
Exit Sub
End If
End With

rng.Select

End Sub

This one looks to see how many visible cells are in the first column after the
filter. If it's more than one (the header row is always counted), then it comes
down one row and resizes (by -1) to ignore the header row.



Owen Vickers wrote:

From an earlier posting on the same subject I found some useful code
that allows me to store the "visible" data/rows in a variable...
thanks to Dave Peterson for that!
The code I used is as follows:
Sub RefreshModel()

Dim rng As Range
Dim maxrow As Integer

Set rng = ActiveSheet.AutoFilter.Range

On Error Resume Next
Set rng = rng.Offset(1, 0).Resize(rng.rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
'rng = rng.SpecialCells(xlCellTypeVisible)


On Error GoTo 0

If rng Is Nothing Then
MsgBox "0 rows"
Else
maxrow = rng.Cells.Count
End If

ActiveSheet.Select
currentrow = 12
Range("B" & currentrow & ":" & "F" & maxrow + 11).Select

End Sub

I get the correct value in the variable for maxrow but it is not
selecting the correct range!
I want to select the range B12 to F:maxrow ...the number of visible
rows but I do not understand the Resize and Offset methods in the code
above meaning that the selction is not working properly and does not
select all the Filtered rows!

What am I missing or not understanding?

Owen

--

Dave Peterson