How do I create a macro to remove blank rows...

[1219Cookie]

I have a worksheet has this formula in column A:
=IF('Task Rating'!B$6=0, 'Task Rating'!B$5, " ")

So it produces blank cell values, but retains the formula.
I've tried to use other macros from this discussion group, but they are
looking for blank cells, so it doesn't work.

I need to create a macro to remove the rows based on the value in any A cell
where the value=""

Any suggestions?

 

[Gary''s Student]

Sub rowkiller()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, 1).Value = "" Then
Rows(i).Delete
End If
Next
End Sub

 

[JLGWhiz]

This is untested, so if you get an error, post back.

Sub delRws()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A2:A" & lr)
For i = lr To 2 Step - 1
If ActiveSheet.Cells(i, 1).Value = "" Then
ActiveSheet.Cells(i, 1).EntireRow.Delete
End If
Next
End Sub

 

[ryguy7272]

This deletes an entire row is there is a blank cell in a certain column, in
this case, ColumnA:
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


This deletes an entire row if the entire row is blank:
Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
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
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

That should cover you in several scenarios!

Ryan---
--
RyGuy

This is untested, so if you get an error, post back.

Sub delRws()
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A2:A" & lr)
For i = lr To 2 Step - 1
If ActiveSheet.Cells(i, 1).Value = "" Then
ActiveSheet.Cells(i, 1).EntireRow.Delete
End If
Next
End Sub

 

[1219Cookie]

I have tried both of the codes above and neither of them removed the rows.
I'm officially stumped here.
--
Kim Cook
Technology Coordinator
General Mills

This deletes an entire row is there is a blank cell in a certain column, in
this case, ColumnA:
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


This deletes an entire row if the entire row is blank:
Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
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
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).EntireRow) = 0 Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

That should cover you in several scenarios!

Ryan---

 

[1219Cookie]

I just created a unique filter-in-place and show all rows macros to do what I
needed for now.

Thanks for all your suggestions!

 

[JLGWhiz]

I am stumped also, because I ran the code on my system with formulas in
column A that produced an empty string ("") and it deleted those rows.
Perhaps you did not have the sheet with the formulas as the ActiveSheet. To
avoid that problem, change "ActiveSheet" in the code to the actual sheet
reference in your workbook.