Loop in a loop syntax

K

Kirk P.

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!!!!
 
V

Vergel Adriano

Kirk,

try it this way:

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

Vergel Adriano

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top