deleting blank rows with formulas

[Zygoid]

Hi All,

I use this macro to delete blank rows. But can's seem to get it t
delete rows with formulas, even thow the row has no values.
Any one know how?

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) =
Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Su

 

[David McRitchie]

A cell with a formula is never empty. A string with no length is not empty.
The following is shorter, but the rows must be entirely empty, so will not
fit you criteria of merely "looking empty".

See my page
http://www.mvps.org/dmcritchie/excel/delempty.htm#rows

for some alternatives.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Earl Kiosterud]

Zy,

It appears you want to delete the row if there's a formula anywhere in it.
Here's a modified (and not very tested) sub that looks for = in the first
character. I've remmed some lines. The added lines have ' ***. the "Set
Found =" line was done with the macro recorder, then a couple of changes.

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range
Dim Found As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
'If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Set Found = Rng.Rows(R).EntireRow.Find(What:="=*", LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False)
If Not Found Is Nothing Then ' ***
Rng.Rows(R).EntireRow.Delete
End If ' ***
N = N + 1
'End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Dave Peterson]

If it's true that you're deleting rows that have cells that evaluate to "", then
you could use a different worksheet function:

If Application.WorksheetFunction.CountBlank(Rng.Rows(R).EntireRow) = 256 Then
instead of:
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then

The top version looks to see if the number of blanks (even those "" cells are
seen as blank in excel's =countblank() worksheet function) is 256 (all of
columns A to IV).

The other just looks for a cell that isn't empty--and those formula cells that
evaluate to "" aren't empty.

(And to make matters worse, if you convert the formulas (="") to values, they're
still not empty!)