Delete row if 3 columns have empty cells in a row

L

Les Stout

Hi all, i have a variable length of spreadsheet with columns N, O & P
with names in. There will never be two or three names next to each other
but there are instances when all 3 will be blank ijn the same row, these
are the Rows that i would like to delete. Could somebody help with some
code if possible please...

Thanks in advance

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***
 
R

Ron de Bruin

Hi Less

Try this for row 1 to 1000

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, "N"), _
.Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

For more info see
http://www.rondebruin.nl/delete.htm
 
L

Les

Hi Less

Try this for row 1 to 1000

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, "N"), _
.Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

For more info seehttp://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm








- Show quoted text -

100% thank you Ron
 
T

Tom Ogilvy

Sub ABC()
Dim r as Range, lastrow as Long
Dim i as Long
set r = activesheet.UsedRange
lastrow = r.rows.count + r.row - 1

for i = lastrow to 1 step - 1
if application.countBlank(cells(i,"N").Resize(1,3)) = 3 then
rows(i).Delete
end if
Next
End Sub
 
L

Les

Hi Less

Try this for row 1 to 1000

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, "N"), _
.Cells(Lrow, "P"))) = 0 Then .Rows(Lrow).Delete

Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

For more info seehttp://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm








- Show quoted text -

Hi Ron, just a question, i assume that it is not possible to use the
xlSpecial cells type option, am i correct ??
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top