M
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.
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.