Deleting Empty Rows
 

Hi everyone,

I have been trying to use a lot of the examples I have found here in the
forums but am so far unable to do so. I have a spreadsheet that will almost
always be different everytime the macro runs. There will always be the same
number of coulmns A:AD but the rows will either increase or decrease in
number. Also there may be multiple blank rows in beteen rows with data. And
there may only be one column on a row that may have something in it. The
first row will be A8 and the last row will always have "Grand Totals" at the
bottom of column A but again I don't know how many rows there might be.

Thanks in advance for any help.

Deleting Empty Rows
 

Hi Darryl


You find code here
http://www.rondebruin.nl/delete.htm

Try
Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

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

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

End Sub




--
Regards Ron De Bruin
http://www.rondebruin.nl



"Darryl" wrote in message ...
Hi everyone,

I have been trying to use a lot of the examples I have found here in the
forums but am so far unable to do so. I have a spreadsheet that will almost
always be different everytime the macro runs. There will always be the same
number of coulmns A:AD but the rows will either increase or decrease in
number. Also there may be multiple blank rows in beteen rows with data. And
there may only be one column on a row that may have something in it. The
first row will be A8 and the last row will always have "Grand Totals" at the
bottom of column A but again I don't know how many rows there might be.

Thanks in advance for any help.

Deleting Empty Rows
 

Ron,

Thanks for the fast reply. I finally found out why this wasn't working
before(Yes, I tried this very code) but apparently the cells have some spaces
and unless I delete these this code doesn't run. Any ideas how to incorporate
those into this code?

Thanks again,

Darryl

"Ron de Bruin" wrote:

Hi Darryl


You find code here
http://www.rondebruin.nl/delete.htm

Try
Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

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

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

End Sub




--
Regards Ron De Bruin
http://www.rondebruin.nl



"Darryl" wrote in message ...
Hi everyone,

I have been trying to use a lot of the examples I have found here in the
forums but am so far unable to do so. I have a spreadsheet that will almost
always be different everytime the macro runs. There will always be the same
number of coulmns A:AD but the rows will either increase or decrease in
number. Also there may be multiple blank rows in beteen rows with data. And
there may only be one column on a row that may have something in it. The
first row will be A8 and the last row will always have "Grand Totals" at the
bottom of column A but again I don't know how many rows there might be.

Thanks in advance for any help.

Deleting Empty Rows
 

You can do a Edit replace first
find what:: push the space bar one time
replace with : do nothing

Good night


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Darryl" wrote in message ...
Ron,

Thanks for the fast reply. I finally found out why this wasn't working
before(Yes, I tried this very code) but apparently the cells have some spaces
and unless I delete these this code doesn't run. Any ideas how to incorporate
those into this code?

Thanks again,

Darryl

"Ron de Bruin" wrote:

Hi Darryl


You find code here
http://www.rondebruin.nl/delete.htm

Try
Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

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

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

End Sub




--
Regards Ron De Bruin
http://www.rondebruin.nl



"Darryl" wrote in message ...
Hi everyone,

I have been trying to use a lot of the examples I have found here in the
forums but am so far unable to do so. I have a spreadsheet that will almost
always be different everytime the macro runs. There will always be the same
number of coulmns A:AD but the rows will either increase or decrease in
number. Also there may be multiple blank rows in beteen rows with data. And
there may only be one column on a row that may have something in it. The
first row will be A8 and the last row will always have "Grand Totals" at the
bottom of column A but again I don't know how many rows there might be.

Thanks in advance for any help.