filtered range

  • Thread starter Thread starter MarkJ
  • Start date Start date
M

MarkJ

I'm trying to work with a range that has had an
autofilter applied to it in VBA. For example:

Dim SimplifiedMethods As Range
Set SimplifiedMethods = Range("SimplifiedMethods")

where the range "SimplifiedMethods" has been filtered on
a couple of criteria and only has about 1/10th of it's
rows showing in the worksheet. Is there any way to have
only the rows that are visible assigned to the Range
object? Thanks in advance.

Mark
 
Hi Mark,

Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range. _
SpecialCells(xlCellTypeVisible)
 
Norman,

Thanks for your help. I'm still having some problems. I
tried this:

Set FilteredRange = Worksheets _
("tblInt9").AutoFilter.Range.SpecialCells _
(xlCellTypeVisible)
Value = FilteredRange(1, 1)
Value = FilteredRange(1, 2)
Value = FilteredRange(1, 3)
Value = FilteredRange(1, 4)
Value = FilteredRange(2, 1)
Value = FilteredRange(2, 2)
Value = FilteredRange(2, 3)
Value = FilteredRange(2, 4)

The values for the first row are correct, but the second
row of the range reverts back to the unfiltered values.
The first four values are from row 5, which is what is
shown after the filter, but the second for values are
from row 1, which is not shown after the filter. Also,

FilteredRange.Rows.Count

results in 1, when there are a couple hundred rows shown
after the filter. Any ideas? Thanks again.

Mark
 
Hi Mark,

It is not immeditely apparent what you are trying to do.

If you are trying to reassure yourself that the correct range is being
addressed, then a simple
Msgbox FilteredRange .Address
may suffice.

Your attempt to reference individual cells of the autofiltered range will
not work - as you have discovered. The reason for this is that , for
example,
FilteredRange(3,3)
refers to a cell two rows down and two rows to the right of the first cell
in the autofilter range and this cell may (or may not!) be in the autofilter
range. Also FilteredRange(1,1)
refers to the first header cell.

Again as you have dicovered, you should not count the number of rows in the
autofiltered range with the statement:
FilteredRange.Rows.Count
This is because typically the autofilter range is compised of a nummber of
discontiguous areas and , in such cases, the Rows.Count will return thr
number of rows in the first area of the autofiltered range. If you need the
row count of the range, you would need to build it with a counter.

The following exemplifies a method for returning the autofilter range
address and row count via messagebox alerts and returns the individual
filtered cell values (row by row to the immediate window.

Sub Tester2()
Dim FilteredRange As Range, rw As Range
Dim iCtr As Long, j As Long

Set FilteredRange = Worksheets("tblInt9").AutoFilter.Range. _
SpecialCells(xlCellTypeVisible)

j = FilteredRange.Columns.Column

For Each rw In FilteredRange.Rows
If rw.Row > FilteredRange.Rows.Row Then
iCtr = iCtr + 1

Debug.Print Cells(rw.Row, j).Value _
& " " & Cells(rw.Row, j + 1) _
& " " & Cells(rw.Row, j + 2) _
& " " & Cells(rw.Row, j + 3)
End If
Next

MsgBox FilteredRange.Address
MsgBox "The number of autofiltered rows is " & iCtr

End Sub
 
Norman,

That cleared it up. I am able to do exactly what I
wanted now. Thanks for your help.

Mark

-----Original Message-----
Hi Mark,

It is not immeditely apparent what you are trying to do.

If you are trying to reassure yourself that the correct range is being
addressed, then a simple
Msgbox FilteredRange .Address
may suffice.

Your attempt to reference individual cells of the autofiltered range will
not work - as you have discovered. The reason for this is that , for
example,
FilteredRange(3,3)
refers to a cell two rows down and two rows to the right of the first cell
in the autofilter range and this cell may (or may not!) be in the autofilter
range. Also FilteredRange(1,1)
refers to the first header cell.

Again as you have dicovered, you should not count the number of rows in the
autofiltered range with the statement:
FilteredRange.Rows.Count
This is because typically the autofilter range is compised of a nummber of
discontiguous areas and , in such cases, the Rows.Count will return thr
number of rows in the first area of the autofiltered range. If you need the
row count of the range, you would need to build it with a counter.

The following exemplifies a method for returning the autofilter range
address and row count via messagebox alerts and returns the individual
filtered cell values (row by row to the immediate window.

Sub Tester2()
Dim FilteredRange As Range, rw As Range
Dim iCtr As Long, j As Long

Set FilteredRange = Worksheets
Click to expand...
("tblInt9").AutoFilter.Range. _
 
you don't need to iterate the entire visible range to get the row count

Dim rng as Range, cell as Range, FilterRange as Range
Dim i as Long
set rng = Worksheets ("tblInt9").AutoFilter.Range
' get the header row out of the range
set rng = rng.offset(1,0).Resize(rng.rows.count-1,1)

on error resume next
set FilterRange = rng.specialcells(xlVisible)
On error goto 0
if FilterRange is nothing then
msgbox "No matches"
Else
' this gives you the visible cells in the first column of the filtered
range. You shouldn't need anything else as you can offset from that for any
other value.

msgbox FilterRange.count
i = 0
for each cell in rng
i = i + 1
msgbox cell & " - " & cell(i,1) & " - " & cell(i,2) & " - " & cell(i,3)
Next
End if
will give you the number of visible rows
 
Hi Tom,
you don't need to iterate the entire visible range to get the row count
Click to expand...
Correct - my error!
i = 0
for each cell in rng
i = i + 1
msgbox cell & " - " & cell(i,1) & " - " & cell(i,2) & " - " & cell(i,3)
Next
End if
Click to expand...

I think that: rng should be: FilterRange
I also think that as used the i variable gives erroneous results and is
redundant.
Amending to :

For Each cell In FilterRange
Debug.Print cell & " - " & cell(1, 1) & " - " & cell(1, 2) & " - " &
cell(1, 3)
Next
End If

worked for me.
 
Correct - my error!
Your code worked - it wasn't an error or any intention to imply there was,
there is just a different way to do it.

Yes, I let my attention wander and made some last minute changes I didn't
think through when I tried to incorporate some explanatory text in the code
and so forth. Problem with trying to integrate with existing code rather
than just write it from scratch. Anyway, this is more like what I
intended:

Sub TesterAAB()
Dim rng As Range, cell As Range, FilterRange As Range
Dim ar As Range
Dim i As Long
Set rng = Worksheets("tblInt9").AutoFilter.Range
' get the header row out of the range
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 1)

On Error Resume Next
Set FilterRange = rng.SpecialCells(xlVisible)
On Error GoTo 0
If FilterRange Is Nothing Then
MsgBox "No matches"
Else

MsgBox FilterRange.Count
i = 0
For Each ar In FilterRange
i = 0
For Each cell In ar
i = i + 1
MsgBox ar(i, 1) & " - " & _
ar(i, 2) & " - " & ar(i, 3) _
& " - " & ar(i, 4)
Next
Next
End If

End Sub

Using the straight
for each cell in FilterRange is also a good way to go as well.
 
Back
Top