Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
I'm new to VBA and I'm working on a project and leaning it
as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
Try this for the activesheet
It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
Oops
Remove the End If Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this for the activesheet It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
That worked Great Thanks Very much I work on and off for
two days on that -----Original Message----- Oops Remove the End If Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this for the activesheet It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
Thanks Tom for your input and that would work but I'm not
sure how to use it -----Original Message----- Ron offered and excellent solution based on your description. Just to add: if you can determine an empty row by having a blank cell in a single column columns(1).SpecialCells(xlblanks).Entirerow.Delet e is also a possibility -- Regards, Tom Ogilvy wrote in message ... That worked Great Thanks Very much I work on and off for two days on that -----Original Message----- Oops Remove the End If Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this for the activesheet It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
Tom's example will delete every row with a empty cell in column A
<columns(1) is the same as columns("A") I will not look if there are values in the other columns -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... Thanks Tom for your input and that would work but I'm not sure how to use it -----Original Message----- Ron offered and excellent solution based on your description. Just to add: if you can determine an empty row by having a blank cell in a single column columns(1).SpecialCells(xlblanks).Entirerow.Delet e is also a possibility -- Regards, Tom Ogilvy wrote in message ... That worked Great Thanks Very much I work on and off for two days on that -----Original Message----- Oops Remove the End If Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this for the activesheet It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
As previously stated:
if you can determine an empty row by having a blank cell in a single column columns(1).SpecialCells(xlblanks).Entirerow.Delete is also a possibility Sub DeleteBank() columns(1).SpecialCells(xlblanks).Entirerow.Delete End sub is how you use it. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Tom's example will delete every row with a empty cell in column A <columns(1) is the same as columns("A") I will not look if there are values in the other columns -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... Thanks Tom for your input and that would work but I'm not sure how to use it -----Original Message----- Ron offered and excellent solution based on your description. Just to add: if you can determine an empty row by having a blank cell in a single column columns(1).SpecialCells(xlblanks).Entirerow.Delet e is also a possibility -- Regards, Tom Ogilvy wrote in message ... That worked Great Thanks Very much I work on and off for two days on that -----Original Message----- Oops Remove the End If Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this for the activesheet It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
remove all blank or empty rows
If there are no Empty cells in column A you can use this to
avoid the error Sub DeleteBank() On Error Resume Next Columns(1).SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0 End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Tom's example will delete every row with a empty cell in column A <columns(1) is the same as columns("A") I will not look if there are values in the other columns -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... Thanks Tom for your input and that would work but I'm not sure how to use it -----Original Message----- Ron offered and excellent solution based on your description. Just to add: if you can determine an empty row by having a blank cell in a single column columns(1).SpecialCells(xlblanks).Entirerow.Delet e is also a possibility -- Regards, Tom Ogilvy wrote in message ... That worked Great Thanks Very much I work on and off for two days on that -----Original Message----- Oops Remove the End If Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Ron de Bruin" wrote in message ... Try this for the activesheet It will loop through all rows with data Sub Example1() Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False For Lrow = .UsedRange.Rows.Count To 1 Step -1 If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete 'This will delete the row if the whole row is empty (all columns) End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub I have add some example code on a webpage http://www.rondebruin.nl/tips.htm Post back if you need help -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl " wrote in message ... I'm new to VBA and I'm working on a project and leaning it as I go. I'm at that point where I think I need a loop that will loop through the rows and remove all blank or empty rows. The worksheet will have a varying number of rows and my have 1 to 4 blank or empty rows in a row (or together) Thanks to all who read this. Thanks to all who replay . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove 10,000+ empty rows below my table? | Excel Discussion (Misc queries) | |||
how to remove empty rows? | Excel Discussion (Misc queries) | |||
How do I remove empty Rows | Excel Discussion (Misc queries) | |||
Remove empty rows in excel? | Excel Discussion (Misc queries) | |||
Remove empty rows | Excel Programming |