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!?

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!?

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!?