using UsedRange.SpecialCells(xlCellTypeVisible).Areas(2)
Thanks Dave
"Dave Peterson" wrote:
You just want to loop through the visible rows?
Dim myVRng As Range
Dim tCell As Range
With ActiveSheet 'ThisWorkbook.Sheets("Orders to be Submitted")
'do the filter
With .AutoFilter.Range
Set myVRng = Nothing
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Cells.Count = 1 Then
'only the header is visible, what should happen?
Else
'ignore the header row and come down one row
'and only look at the first column???
Set myVRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
For Each tCell In myVRng.Cells 'why rows?
'do the work
MsgBox tCell.Address
Next tCell
End If
End With
End With
TD wrote:
Hi all,
I was using the following code:
Dim Tcell As Range
With ThisWorkbook.Sheets("Orders to be Submitted")
.Activate
Data = Trim(.Cells(ActiveCell.Row, 4))
.AutoFilterMode = False
.Range("1:1").AutoFilter
.Range("1:1").AutoFilter Field:=4, Criteria1:=Data
End With
For Each Tcell In
Sheet6.UsedRange.SpecialCells(xlCellTypeVisible).A reas(2).Rows
....
It worked fine until eventually I was getting an error because areas(2)
wasn't defined. I found this only happened when my autofilter would give me
back rows directly under the header row (2-5 for example). This is rather
frustrating in that I was hoping the behavior would be the same in all
cases(no header row & Area2 defined). Am I doing something wrong or is there
a better way to deal with this?
Thanks much
-TD
--
Dave Peterson
|