go over all the rows in data of autofilter

[Guest]

Hi,

I made autofilter by tow fields and I get 131 filtered rows.
Rows 2058-2102 and 3758-3842.
I want to over on all rows and get data for it.
When I do it I success to over only on the 45 first sequential rows.

the start of the code is:
With filterRng
.AutoFilter Field:=37, Criteria1:=appName
.AutoFilter Field:=38, Criteria1:=activityType

If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Cells.Count = 1 Then
'No filter row
'MsgBox "nothing visible in the filter!"
Exit Sub
End If

Set myRng = .Resize(.Rows.Count - 1, .Columns.Count) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
End With

myRng iclude only 45 lines (2058-2102 rows). Why?

How can I move over all the rows?
Thanks,
Shlomit

 

[Jim Rech]

Looks to me that it should work. Is FilterRng correct? What happens if you
replace

With filterRng

with

With Range("_FilterDatabase")

--
Jim
| Hi,
|
| I made autofilter by tow fields and I get 131 filtered rows.
| Rows 2058-2102 and 3758-3842.
| I want to over on all rows and get data for it.
| When I do it I success to over only on the 45 first sequential rows.
|
| the start of the code is:
| With filterRng
| .AutoFilter Field:=37, Criteria1:=appName
| .AutoFilter Field:=38, Criteria1:=activityType
|
| If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
| .Cells.Count = 1 Then
| 'No filter row
| 'MsgBox "nothing visible in the filter!"
| Exit Sub
| End If
|
| Set myRng = .Resize(.Rows.Count - 1, .Columns.Count) _
| .Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
| End With
|
| myRng iclude only 45 lines (2058-2102 rows). Why?
|
| How can I move over all the rows?
| Thanks,
| Shlomit
|

 

[Guest]

The problem is different,
"myRng" contain all the cells in the filter but "myRng.Rows.Count" return 45
instead if 131. Why? (It sruck where I have jump of rows)
Also I want to run row by row and not on all the cells in the range. How can
I do it?


thanks,
Shlomit

 

[Jim Rech]

Sorry if my response was unclear. What I should have said was that I set up
an example similar to yours and I got the right answer for the number of
visible rows, that is, the sum of the rows in both areas. So I thought
perhaps the range variable FilterRng was not correctly set. Trying the
other code I suggested would address that by not relying on FilterRng.
--
Jim
| The problem is different,
| "myRng" contain all the cells in the filter but "myRng.Rows.Count" return
45
| instead if 131. Why? (It sruck where I have jump of rows)
| Also I want to run row by row and not on all the cells in the range. How
can
| I do it?
|
|
| thanks,
| Shlomit
|
|
| "Jim Rech" wrote:
|
| > Looks to me that it should work. Is FilterRng correct? What happens if
you
| > replace
| >
| > With filterRng
| >
| > with
| >
| > With Range("_FilterDatabase")
| >
| > --
| > Jim
| > | > | Hi,
| > |
| > | I made autofilter by tow fields and I get 131 filtered rows.
| > | Rows 2058-2102 and 3758-3842.
| > | I want to over on all rows and get data for it.
| > | When I do it I success to over only on the 45 first sequential rows.
| > |
| > | the start of the code is:
| > | With filterRng
| > | .AutoFilter Field:=37, Criteria1:=appName
| > | .AutoFilter Field:=38, Criteria1:=activityType
| > |
| > | If .Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
| > | .Cells.Count = 1 Then
| > | 'No filter row
| > | 'MsgBox "nothing visible in the filter!"
| > | Exit Sub
| > | End If
| > |
| > | Set myRng = .Resize(.Rows.Count - 1, .Columns.Count) _
| > | .Offset(1,
0).Cells.SpecialCells(xlCellTypeVisible)
| > | End With
| > |
| > | myRng iclude only 45 lines (2058-2102 rows). Why?
| > |
| > | How can I move over all the rows?
| > | Thanks,
| > | Shlomit
| > |
| >
| >
| >

 

[Dave Peterson]

msgbox myrng.rows.count

You'll be returning the number of rows in the first area in that range and the
range is just the visible rows in the filter. I'm guessing that the visible
rows in that filtered range are not contiguous.

If you want the number of visible rows, you could use something like:

Dim myRng As Range
With ActiveSheet.AutoFilter.Range
Set myRng = .Resize(.Rows.Count - 1, 1) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible)
End With

MsgBox myRng.Cells.Count

Notice that it's only looking at a single column (in the .resize() portion).

 

[Tom Ogilvy]

Another way to do the same thing:

Dim myRng As Range
With ActiveSheet.AutoFilter.Range
Set myRng = .Columns(1).SpecialCells(xlVisible)
End With
msgbox myRng.Count - 1

 

[Guest]

O.K. Thanks for all of you.
I get the answer of Dave and "myRng.Cells.Count" return me the correct count
(i.e. "myRng.Rows.Count" return the first contiguous cells)

But how can I over on all the visible cells and know its row number? I need
to get data from all the visible rows.

Thanks,
Shlomit

Another way to do the same thing:

Dim myRng As Range
With ActiveSheet.AutoFilter.Range
Set myRng = .Columns(1).SpecialCells(xlVisible)
End With
msgbox myRng.Count - 1

 

[Guest]

Hi, It's ok.
I found the answer
" For Each cell In myRng" each cell return the correct row number.

Thanks a lot
shlomit

O.K. Thanks for all of you.
I get the answer of Dave and "myRng.Cells.Count" return me the correct count
(i.e. "myRng.Rows.Count" return the first contiguous cells)

But how can I over on all the visible cells and know its row number? I need
to get data from all the visible rows.

Thanks,
Shlomit