Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows
Good afternoon all
Does anyone have something nice and quick that can delete all empty rows in the UsedRange Thanks for your time Foss |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows
Try this Foss
Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow 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 http://www.rondebruin.nl "Foss" wrote in message ... Good afternoon all, Does anyone have something nice and quick that can delete all empty rows in the UsedRange? Thanks for your time, Foss |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows
If you can make the determination on a single column (if a cell in that
column is blank, then delete) and the cells are actually blank, not just appear blank but contain a formula, and the number of non-contiguous areas are less thant 8192 you can quickly delete with On Error Resume Next Columns(1).Specialcells(xlBlanks).EntireRow.Delete On Error goto 0 1 specifies column A, adjust as needed. -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Try this Foss Sub Example1() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Firstrow = ActiveSheet.UsedRange.Cells(1).Row Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1 With ActiveSheet .DisplayPageBreaks = False For Lrow = Lastrow To Firstrow 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 http://www.rondebruin.nl "Foss" wrote in message ... Good afternoon all, Does anyone have something nice and quick that can delete all empty rows in the UsedRange? Thanks for your time, Foss |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows
Hi Tom
Thanks for the tip, that's a handy one Cheers Fos ----- Tom Ogilvy wrote: ---- If you can make the determination on a single column (if a cell in tha column is blank, then delete) and the cells are actually blank, not jus appear blank but contain a formula, and the number of non-contiguous area are less thant 8192 you can quickly delete wit On Error Resume Nex Columns(1).Specialcells(xlBlanks).EntireRow.Delet On Error goto 1 specifies column A, adjust as needed -- Regards Tom Ogilv "Ron de Bruin" wrote in messag .. Try this Fos Sub Example1( Dim Firstrow As Lon Dim Lastrow As Lon Dim Lrow As Lon Dim CalcMode As Lon With Applicatio CalcMode = .Calculatio .Calculation = xlCalculationManua .ScreenUpdating = Fals End Wit Firstrow = ActiveSheet.UsedRange.Cells(1).Ro Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - With ActiveShee .DisplayPageBreaks = Fals For Lrow = Lastrow To Firstrow Step - If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delet 'This will delete the row if the whole row is empty (al columns Nex End Wit With Applicatio .ScreenUpdating = Tru .Calculation = CalcMod End Wit End Su -- Regards Ron de Brui http://www.rondebruin.n "Foss" wrote in messag .. Good afternoon all Does anyone have something nice and quick that can delete all empty row in the UsedRange Thanks for your time Fos |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting empty rows
Hi Ron
Thanks very much, that worked a treat Cheers Fos ----- Ron de Bruin wrote: ---- Try this Fos Sub Example1( Dim Firstrow As Lon Dim Lastrow As Lon Dim Lrow As Lon Dim CalcMode As Lon With Applicatio CalcMode = .Calculatio .Calculation = xlCalculationManua .ScreenUpdating = Fals End Wit Firstrow = ActiveSheet.UsedRange.Cells(1).Ro Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - With ActiveShee .DisplayPageBreaks = Fals For Lrow = Lastrow To Firstrow Step - If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delet 'This will delete the row if the whole row is empty (all columns Nex End Wit With Applicatio .ScreenUpdating = Tru .Calculation = CalcMod End Wit End Su -- Regards Ron de Brui http://www.rondebruin.n "Foss" wrote in message .. Good afternoon all Does anyone have something nice and quick that can delete all empty rows in the UsedRange Thanks for your time Fos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting empty rows | New Users to Excel | |||
deleting empty rows | Excel Discussion (Misc queries) | |||
Deleting empty rows automatically | Excel Discussion (Misc queries) | |||
Deleting All Empty Rows | Excel Discussion (Misc queries) | |||
Deleting unwanted and empty rows... | Excel Discussion (Misc queries) |