To delete rows when more than one cell is blank

[maxc246]

Since it took me a while to fine tune this macro, I thought I'd post it
in case anyone else can use it.

This macro selects 3 consecutive cells at a time and looks in those
cells for values. If all 3 cells are blank, that row is deleted. If
any cell in the selection contains a value, the row is left in tact.

Sub DeleteRowsWithSomeBlankCells()
Dim B As Long
Dim X As Variant
Dim Y As Long
Dim ThisCol As String
Y = 1
For B = 1000 To 1 Step -1
' Start at row 1000 and work up. Change 1000 to the number of rows
' in your worksheet. This prevents the macro from skipping a row if
' the row above it was deleted, since Excel shifts up after deleting
' a row.
Range("A" & B & ":" & "C" & B).Select
' Selects the 3 cells in columns A, B and C. Change A and C
' if you plan to search other columns.
For Each X In Selection
If X.Text = "" Then
If Y / 3 = 1 Then
'Only deletes the row once it has checked all 3 cells.
Rows(X.Row).Select
Selection.Delete Shift:=xlUp
Y = 1 'Reset Y
Else
ThisCol = Chr(65 + X.Column)
' This is needed to convert X.column to a character
' instead of a number. 64 would be the current value
' of X.Column. 65 is X.Column + 1, needed because
' 'm incrementing the active cell by 1 column.
Y = Y + 1
Range(ThisCol & X.Row).Activate
' Moves the active cell in the selection to the right
' 1 cell.
End If
Else
Y = 1
Exit For
' It found a value in a cell and is moving on to the
' row above.
End If
Next
Next
End Sub


Thanks to Tom Ogilvy for posting his elegant ThisCol solution for
converting a column's numerical value to a string by using the Char
function and adding 64 to the ASCII value of x.column. He posted that
back in 1998. It's still being used, Tom!

Max.

 

[Ron de Bruin]

You can do it like this without selecting

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = EndRow To StartRow Step -1

If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "C"))) = 0 Then .Rows(Lrow).Delete

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

 

[maxc246]

Hey, that's pretty slick, Ron. Would you mind posting it again with
some line comments? I'm not familiar with some of the properties and
methods you called. I'd like to learn the ideas behind what you've
done here.

Max.

 

[Ron de Bruin]

Hi Max

Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

' Turn off screenupdating and save calc setting and change it to manual
' You code run faster then
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' set to normal view if you are in page break view, also for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Loop from row 1000 -1 in steps of -1
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = EndRow To StartRow Step -1

'We use the worksheetfunction counta to see if there is somthing in A:C range of the row in the loop
'If 0 then all cells are empty
If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "C"))) = 0 Then .Rows(Lrow).Delete

Next
End With

'Restore settings
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl

Hey, that's pretty slick, Ron. Would you mind posting it again with
some line comments? I'm not familiar with some of the properties and
methods you called. I'd like to learn the ideas behind what you've
done here.

Max.

 

[maxc246]

Hey thanks Ron! It's that CountA function I was most unfamiliar with.
That's really handy! And you're right. Yours runs WAY faster than
mine.

Thanks for taking the time to explain.

Max.

Hi Max

Sub Example()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

' Turn off screenupdating and save calc setting and change it to manual
' You code run faster then
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

' set to normal view if you are in page break view, also for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Loop from row 1000 -1 in steps of -1
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1000

For Lrow = EndRow To StartRow Step -1

'We use the worksheetfunction counta to see if there is somthing in A:C range of the row in the loop
'If 0 then all cells are empty
If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "C"))) = 0 Then .Rows(Lrow).Delete

Next
End With

'Restore settings
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub