Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
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" wrote in message ... 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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
On Apr 18, 6:59 pm, "Ron de Bruin" 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" wrote in l... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
On Apr 18, 6:59 pm, "Ron de Bruin" 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" wrote in l... 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 ?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
No, not with more columns to check
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Les" wrote in message oups.com... On Apr 18, 6:59 pm, "Ron de Bruin" 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" wrote in l... 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 ?? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
Amazing, thank you Tom.... always a life saver... :-D
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if 3 columns have empty cells in a row
Ek het so gedink, Danke Ron. You people are just amazing thanks again...
Les Stout *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there an easy way to delete empty columns? | Excel Worksheet Functions | |||
How to delete at once empty columns in a table? | Excel Discussion (Misc queries) | |||
Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
Delete rows with empty cells in columns B&C | Excel Discussion (Misc queries) | |||
Delete empty array columns | Excel Programming |