Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro to delete entire rows where certain range of cells are blank
I'm looking for some help. I have looked around online but I can't quite find the macro I need.
Basically, in a table, every row has a value in Columns A:C. Certain rows have values from D:N however, some rows are completely blank between D:N. What I want to do is completely delete any row in its entirety, if columns D:N are blank. I have a manual workaround but really a macro is what I'm looking for. Can anyone help please!? Last edited by cameron4987 : June 26th 12 at 12:21 PM |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire rows where certain range of cells are blank
Sub DeleteRows_If_D_to_N_MT()
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 'adjust to suit For lRow = EndRow To StartRow Step -1 If Application.CountA(.Range(.Cells(lRow, "D"), _ .Cells(lRow, "N"))) = 0 Then .Rows(lRow).Delete Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Gord On Tue, 26 Jun 2012 11:17:11 +0000, cameron4987 wrote: I'm looking for some help. I have looked around online but I can't quite find the macro I need. Basically, in a table, every row has a value in Columns A:C. Certain rows have values from D:N however, some rows are completely blank between D:N. What I want to do is completely delete any row in its entirety, if columns D:N are blank. I have a manual workaround but really a macro is what I'm looking for. Can anyone help please!? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete entire rows where certain range of cells areblank
hi Cameron,
Sub Macro1() Dim n As Integer, i As Long For i = Range("A65536").End(xlUp).Row To 1 Step -1 n = Evaluate("CountA(D" & i & ":N" & i & ")") If n = 0 Then Rows(i).Delete ' or Rows(i).Delete Shift:=xlUp Next End Sub -- isabelle Le 2012-06-26 07:17, cameron4987 a écrit : I'm looking for some help. I have looked around online but I can't quite find the macro I need. Basically, in a table, every row has a value in Columns A:C. Certain rows have values from D:N however, some rows are completely blank between D:N. What I want to do is completely delete any row in its entirety, if columns D:N are blank. I have a manual workaround but really a macro is what I'm looking for. Can anyone help please!? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I selectivly delte rows with a macro ? | Excel Programming | |||
MACRO HELP - deleting rows containing a range of blank cells | Excel Discussion (Misc queries) | |||
delte multiple blank rows in excel | Excel Worksheet Functions | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Delte blank rows between different sections of data | Excel Programming |