How do I create a macro to remove blank rows...
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).E ntireRow) = 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
"JLGWhiz" wrote:
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" wrote in message
...
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?
--
Kim Cook
Technology Coordinator
General Mills
|