nestint 2 loops

A

april

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
 
D

Don Guillett

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
 

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