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 ***

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Les Stout" <(E-Mail Removed)> wrote in message news:%(E-Mail Removed)...
> 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 ***

On Apr 18, 6:59 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> 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
>
>
>
> "Les Stout" <anonym...@devdex.com> wrote in messagenews:%(E-Mail Removed)...
> > 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 Developersdexhttp://www.developersdex.com***- Hide quoted text -
>
> - Show quoted text -

100% thank you Ron

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

--
Regards,
Tom Ogilvy


"Les Stout" wrote:

> 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 ***
>

On Apr 18, 6:59 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
> 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
>
>
>
> "Les Stout" <anonym...@devdex.com> wrote in messagenews:%(E-Mail Removed)...
> > 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 Developersdexhttp://www.developersdex.com***- Hide quoted text -
>
> - 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 ??

No, not with more columns to check

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Les" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> On Apr 18, 6:59 pm, "Ron de Bruin" <rondebr...@kabelfoon.nl> wrote:
>> 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
>>
>>
>>
>> "Les Stout" <anonym...@devdex.com> wrote in messagenews:%(E-Mail Removed)...
>> > 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 Developersdexhttp://www.developersdex.com***- Hide quoted text -
>>
>> - 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 ??
>

Amazing, thank you Tom.... always a life saver... :-D

Les Stout

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

Ek het so gedink, Danke Ron. You people are just amazing thanks again...

Les Stout

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