nestint 2 loops
 

For blanks change criteria to
Criteria1:="="

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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

nestint 2 loops
 

thanks for your help. this doesn't quite give me the results that i'm
looking for because filtering on blank columns doesn't take into account that
some rows are blank in one column but not others. this is a financial
statement that i am working with so you have this kind of format.
Col 1 col 2 col3
Assets
cash 200
receivable 100
total current assets
and so forth. also the macro doesn't loop through each worksheet. the
section of the macro in my question that deletes blank rows i works. and i
know that the part that loops through the workbook works. i just can't seem
to make the two work together

while your macro didn't solve my immediate problem, i have copied it into my
helpful macros book and will find future use for it.

aprilshowers


"Don Guillett" wrote:

For blanks change criteria to
Criteria1:="="

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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

nestint 2 loops
 

Your sheet loop wont work until you correct the last line
Are you saying you want to delete all rows that have nothing in ANY column?
I would like to see before/after.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"april" wrote in message
...
thanks for your help. this doesn't quite give me the results that i'm
looking for because filtering on blank columns doesn't take into account
that
some rows are blank in one column but not others. this is a financial
statement that i am working with so you have this kind of format.
Col 1 col 2 col3
Assets
cash 200
receivable 100
total current assets
and so forth. also the macro doesn't loop through each worksheet. the
section of the macro in my question that deletes blank rows i works. and
i
know that the part that loops through the workbook works. i just can't
seem
to make the two work together

while your macro didn't solve my immediate problem, i have copied it into
my
helpful macros book and will find future use for it.

aprilshowers


"Don Guillett" wrote:

For blanks change criteria to
Criteria1:="="

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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

nestint 2 loops
 

i looked over my macro and found the error - with a little tinkering, i made
the thing work. yes, i was saying that i wanted to delete all rows that had
nothing in any column.
thanks
--
aprilshowers


"Don Guillett" wrote:


Your sheet loop wont work until you correct the last line
Are you saying you want to delete all rows that have nothing in ANY column?
I would like to see before/after.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"april" wrote in message
...
thanks for your help. this doesn't quite give me the results that i'm
looking for because filtering on blank columns doesn't take into account
that
some rows are blank in one column but not others. this is a financial
statement that i am working with so you have this kind of format.
Col 1 col 2 col3
Assets
cash 200
receivable 100
total current assets
and so forth. also the macro doesn't loop through each worksheet. the
section of the macro in my question that deletes blank rows i works. and
i
know that the part that loops through the workbook works. i just can't
seem
to make the two work together

while your macro didn't solve my immediate problem, i have copied it into
my
helpful macros book and will find future use for it.

aprilshowers


"Don Guillett" wrote:

For blanks change criteria to
Criteria1:="="

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message news:...
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