using UsedRange.SpecialCells(xlCellTypeVisible).Areas(2)

[Guest]

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).Areas(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]

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

 

[Guest]

The problem is not with where your cells are, but rather how they take shape.
Areas should only be used when you have non-contiguous blocks of cells. If
you have one block, then you have a range with only one area. It sounds like
your problem is that you only have a contiguous block of cells left after the
autofilter. Since you only have one area, Areas(2) doesn't exits.

 

[Guest]

Thanks Dave

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

 

[Guest]

Thanks, makes sense!

The problem is not with where your cells are, but rather how they take shape.
Areas should only be used when you have non-contiguous blocks of cells. If
you have one block, then you have a range with only one area. It sounds like
your problem is that you only have a contiguous block of cells left after the
autofilter. Since you only have one area, Areas(2) doesn't exits.