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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To delete rows when more than one cell is blank
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 -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To delete rows when more than one cell is blank
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 wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To delete rows when more than one cell is blank
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 wrote in message oups.com... 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 wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
To delete rows when more than one cell is blank
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. Ron de Bruin wrote: 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 wrote in message oups.com... 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 wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl |
Reply |
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 |