Loop in a loop syntax
 

I'm trying to loop through all worksheets in the active workbook, then loop
through each row in the worksheet and delete all rows with zero in column AA.
I'm having trouble with syntax - here's what I've got:

Sub DeleteRowsTest()

Dim w As Worksheet
Dim FirstRow, LastRow, CurRow As Integer
Dim col As String

FirstRow = 5
LastRow = 20000
col = "AA"

For Each w In Worksheets
For CurRow = LastRow To FirstRow Step -1
If w.Cells(CurRow, col) = 0 Then
w.Rows(CurRow).Delete
Next CurRow
Exit For
Next

End Sub

Help!!!!

Loop in a loop syntax
 

Kirk,

try it this way:

For Each w In Worksheets
For CurRow = w.UsedRange.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
To FirstRow Step -1
If w.Cells(CurRow, col) = 0 Then
w.Rows(CurRow).Delete
Next CurRow
Exit For
Next


--
Hope that helps.

Vergel Adriano


"Kirk P." wrote:

I'm trying to loop through all worksheets in the active workbook, then loop
through each row in the worksheet and delete all rows with zero in column AA.
I'm having trouble with syntax - here's what I've got:

Sub DeleteRowsTest()

Dim w As Worksheet
Dim FirstRow, LastRow, CurRow As Integer
Dim col As String

FirstRow = 5
LastRow = 20000
col = "AA"

For Each w In Worksheets
For CurRow = LastRow To FirstRow Step -1
If w.Cells(CurRow, col) = 0 Then
w.Rows(CurRow).Delete
Next CurRow
Exit For
Next

End Sub

Help!!!!

Loop in a loop syntax
 

Correction... use this one instead.

Sub DeleteRowsTest()

Dim w As Worksheet
Dim FirstRow As Long, LastRow As Long, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

For Each w In Worksheets
LastRow = w.UsedRange.SpecialCells(xlCellTypeLastCell).Row
If LastRow = FirstRow Then
For CurRow = LastRow To FirstRow Step -1
If w.Cells(CurRow, col) = 0 Then w.Rows(CurRow).Delete
Next CurRow
End If
Next w


End Sub



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Kirk,

try it this way:

For Each w In Worksheets
For CurRow = w.UsedRange.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
To FirstRow Step -1
If w.Cells(CurRow, col) = 0 Then
w.Rows(CurRow).Delete
Next CurRow
Exit For
Next


--
Hope that helps.

Vergel Adriano


"Kirk P." wrote:

I'm trying to loop through all worksheets in the active workbook, then loop
through each row in the worksheet and delete all rows with zero in column AA.
I'm having trouble with syntax - here's what I've got:

Sub DeleteRowsTest()

Dim w As Worksheet
Dim FirstRow, LastRow, CurRow As Integer
Dim col As String

FirstRow = 5
LastRow = 20000
col = "AA"

For Each w In Worksheets
For CurRow = LastRow To FirstRow Step -1
If w.Cells(CurRow, col) = 0 Then
w.Rows(CurRow).Delete
Next CurRow
Exit For
Next

End Sub

Help!!!!