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)


---
Regards,
Norman



"MarkJ" wrote in message
...
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

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

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

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


---
Regards,
Norman



"MarkJ" wrote in message
...
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,

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

---
Regards,
Norman


wrote in message
...
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

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

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


---
Regards,
Norman



"MarkJ" wrote in message
...
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


.

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
("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

---
Regards,
Norman


wrote in message
...
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

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

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


---
Regards,
Norman



"MarkJ" wrote in message
...
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


.



.

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

--
Regards,
Tom Ogilvy

Hi Tom,

you don't need to iterate the entire visible range to get the row count
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

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.


---
Regards,
Norman


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

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.

--
Regards,
Tom Ogilvy



"Norman Jones" wrote in message
...
Hi Tom,

you don't need to iterate the entire visible range to get the row count
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

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.


---
Regards,
Norman