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

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

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

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

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