Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To delete rows when more than one cell is blank
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I delete blank rows from excel without selecting them? | Excel Worksheet Functions | |||
Excel Novice: Delete blank rows | Excel Discussion (Misc queries) | |||
delete blank rows | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) | |||
blank cell turns to 0 | New Users to Excel |