nestint 2 loops
 

i am trying to go through each sheet in a workbook and remove the blank rows.
i am using this macro

Sub RemoveBlankRows()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Select
Dim FinalRow As Long
Dim RowCounter As Long
Dim ObjVariable As Object
Set ObjVariable = Application.WorksheetFunction
Application.ScreenUpdating = False
FinalRow = ActiveSheet.UsedRange.Rows.Count
For RowCounter = FinalRow To 1 Step -1
If ObjVariable.CountA(Rows(RowCounter)) = 0 Then
Rows(RowCounter).Delete
End If
Next RowCounter
Next my.Sheet
End Sub
--

however, i get the error message "Invalid Next control varialbe reference".
Any suggestions?

Thanks in advance for your help.
aprilshowers

nestint 2 loops
 

try this simple macro to check col A

Sub delete0rows1()
For Each ms In Worksheets

lr = ms.Cells(rows.Count, "a").End(xlUp).Row
With ms.Range("a1:a" & lr)
.AutoFilter Field:=1, Criteria1:="0"
.EntireRow.Delete
.AutoFilter
End With

next ms
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"april" wrote in message
...
i am trying to go through each sheet in a workbook and remove the blank
rows.
i am using this macro

Sub RemoveBlankRows()
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.Select
Dim FinalRow As Long
Dim RowCounter As Long
Dim ObjVariable As Object
Set ObjVariable = Application.WorksheetFunction
Application.ScreenUpdating = False
FinalRow = ActiveSheet.UsedRange.Rows.Count
For RowCounter = FinalRow To 1 Step -1
If ObjVariable.CountA(Rows(RowCounter)) = 0 Then
Rows(RowCounter).Delete
End If
Next RowCounter
Next my.Sheet
End Sub
--

however, i get the error message "Invalid Next control varialbe
reference".
Any suggestions?

Thanks in advance for your help.
aprilshowers