Selecting Filtered records only when AutoFilter is on

  • Thread starter Thread starter Owen Vickers
  • Start date Start date
O

Owen Vickers

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
 
I'm probably not the person to be answering this, but did you mean you want the rows that are hidden in the filter to be or to not be selected?

If maxrow is say 3, so that your range to select would be B12:F14 (maxrow+11), and say rows 12 and 13 are hidden because of filtering, you want only B14:F14 to be selected, or did you want B12:F14 to be selected even tho they are filtered?

As is, this macro would select B12:F14 even if rows 12 and 13 are filtered. If you would only want B14:F14 to be selected, your Range line should be:

Range("B" & currentrow & ":" & "F" & maxrow + 11).SpecialCells(xlCellTypeVisible).Select

HTH
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
 
Hi Owen,
Your select statement will select a single continuous range of cells
with maxrow rows, whether they are visible or not.
Maybe you want;
rng.Offset(1, 0).Resize(rng.rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible).Select

This will select the filtered cells in your range.

If you are going to copy your filtered range to somewhere, then you
need not even use SpecialCells. Simply put on the filter then do a
copy on rng (look up help on the copy method). Only the visible cells
will copy.

regards
Paul
 
Owen: I do filter, copy, and paste all the time. But I use Paste
Special>>Values Only (and then sometimes will go back and add Columns Widths
and Formats). This method leaves some things behind, I'm sure. But if it
will work for you, here's the code I use to copy a filtered range and paste
to a new sheet.

' Find end of data, start for lists
Range("B65536").End(xlUp).Select
LastRow = ActiveCell.Row

' Select range and copy
Range("A1:U" & LastRow).Select '<<Change A1 to your starting cell
Selection.Copy

' Open Calc_Stats workbook
Set wb2 = Workbooks.Open(strFPath & "Calc_Stats.xls")

' Paste in data
wb2.Activate
Sheets("Sheet1").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select

HTH
Ed
 
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.
 
Back
Top