The following is a full example of how to work with AutoFiltered range. It
includes comments on what the code is doing plus some commented out code that
you probably won't need but is there for information for selecting all the
visible data. I have left uncommented the line of code to select an
individual column.
The following you may be aware of but just in case it saves you having to
get back to me if you do not know about it.
Note: When using Offset(row,column) the value of offset is the number of
times you would have to press an arrow key to arrive at the row or column. It
is not a count of the columns or column number.
Also note that a space and underscore at the end of a line is a line break
in anotherwise single line of code.
Sub SelectAutoFilteredData()
Dim rngVisible As Range
Dim c As Range
'Should always test for AutofilterMode and Filter Mode _
to ensure working with a filtered range to avoid code errors.
If Sheets("Sheet1").AutoFilterMode Then 'Test if filter arrows present
If Sheets("Sheet1").FilterMode Then 'Test if actually filtered
With Sheets("Sheet1").AutoFilter.Range
'Next line returns number of visible cells divided _
by number of columns in autofilter range. _
If greater than 1 then some data is visible. _
Equal to 1 then only column headers visible
If .SpecialCells(xlCellTypeVisible).Count / _
.Columns.Count > 1 Then
'Select all visible data. Offset to row below column _
headers and resize to one row less to account for _
not including column headers.
'Set rngVisible = .Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible)
'Alternative to previous line of code. _
Defaults to number of columns in AutoFilter.Range _
without specifying first or last Column numbers.
'Set rngVisible = .Offset(1) _
.Resize(.Rows.Count - 1) _
.SpecialCells(xlCellTypeVisible)
'To select only one column, set the Offset _
1 row down and X columns across and then _
Resize to only 1 column wide.
Set rngVisible = .Offset(1, 3) _
.Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End If
End With
Else
'used for testing
MsgBox "No filters have actually been set"
End If
Else
'Used for testing
MsgBox "AutoFilter mode has not been set on the worksheet"
End If
For Each c In rngVisible
MsgBox c.Address 'for testing only
'Insert your code here in lieu of msgbox
'Something like the following:-
'If c.value = "Whatever" then
'your code etc
'End If
Next c
End Sub
