ExcelBanter - View Single Post - using UsedRange.SpecialCells(xlCellTypeVisible).Areas(2)

TD

Thanks, makes sense!

"eAlchemist" wrote:

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.

--
Chris Farkas
Excel/Access Developer
http://www.eAlchemy.biz

"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