delete all blank rows in a spreadsheet
 

How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

you could have a Helper Colum in col a ,,, that counts all the non empty
cells in that row,, uning the countif function

then ya could sort by that row ,,


Rich


"Richard" wrote:

How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Richard,

Try Something like:

'================
Public Sub Tester1()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rcell As Range
Dim delRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = Workbooks("A.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.UsedRange

On Error GoTo XIT

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

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

For Each rcell In Rng.Cells
If Application.CountA(rcell.EntireRow) = 0 Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<================


---
Regards,
Norman



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may
be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may
be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Norman,

Thanks for the prompt reply.

I have ran the code as a macro and the code runs, but does nothing other
than the screen 'flashing' once. The blank rows remain in place.

"Norman Jones" wrote:

Hi Richard,

Try Something like:

'================
Public Sub Tester1()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rcell As Range
Dim delRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = Workbooks("A.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.UsedRange

On Error GoTo XIT

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

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

For Each rcell In Rng.Cells
If Application.CountA(rcell.EntireRow) = 0 Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<================


---
Regards,
Norman



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may
be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Thanks,

That does help, but used in conjunction with a second helper column numbered
in sequence and used as the second sort criteria so that the rows can be
reordered at the end. (unless I have misunderstood you)

"Rich Mcc" wrote:

you could have a Helper Colum in col a ,,, that counts all the non empty
cells in that row,, uning the countif function

then ya could sort by that row ,,


Rich


"Richard" wrote:

How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Nigel,

Thanks. When I ran the code an error was thown up. The debugger highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the columns
have data in every non blank row i.e. if I sorted by column A, there may
be a
row with a blank cell in column A, but another column (say AX) that may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Richard,

I tested my code before posting it and was happy that it worked.

Set WB = Workbooks("A.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Have you, as suggested, changed the workbook and worksheet names to accord
with your scenario?

Are you sure that your empty rows are truly emty, i.e. no cell in the row
contains any entry or formula?


---
Regards,
Norman



"Richard" wrote in message
...
Norman,

Thanks for the prompt reply.

I have ran the code as a macro and the code runs, but does nothing other
than the screen 'flashing' once. The blank rows remain in place.

"Norman Jones" wrote:

Hi Richard,

Try Something like:

'================
Public Sub Tester1()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rcell As Range
Dim delRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = Workbooks("A.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.UsedRange

On Error GoTo XIT

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

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

For Each rcell In Rng.Cells
If Application.CountA(rcell.EntireRow) = 0 Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<================


---
Regards,
Norman



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Norman,

I did change the code, I also altered the rows to the following in case I
had inserted a typo:

'Set WB = Workbooks("6036 Pay Cert.xls") '<<===== CHANGE
'Set SH = WB.Sheets("6") '<<===== CHANGE
Set Rng = ActiveSheet.UsedRange 'SH.UsedRange

The same thing happened.

I have also manually deleted contents from a few blank rows, to ensure that
the rows are blank. Again, the same thing happened.

The cells have borders, but I have assumed this should not affect it.

"Norman Jones" wrote:

Hi Richard,

I tested my code before posting it and was happy that it worked.

Set WB = Workbooks("A.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE

Have you, as suggested, changed the workbook and worksheet names to accord
with your scenario?

Are you sure that your empty rows are truly emty, i.e. no cell in the row
contains any entry or formula?


---
Regards,
Norman



"Richard" wrote in message
...
Norman,

Thanks for the prompt reply.

I have ran the code as a macro and the code runs, but does nothing other
than the screen 'flashing' once. The blank rows remain in place.

"Norman Jones" wrote:

Hi Richard,

Try Something like:

'================
Public Sub Tester1()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rcell As Range
Dim delRng As Range
Dim CalcMode As Long
Dim ViewMode As Long

Set WB = Workbooks("A.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set Rng = SH.UsedRange

On Error GoTo XIT

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

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

For Each rcell In Rng.Cells
If Application.CountA(rcell.EntireRow) = 0 Then
If delRng Is Nothing Then
Set delRng = rcell
Else
Set delRng = Union(rcell, delRng)
End If
End If
Next rcell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
End If

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<================


---
Regards,
Norman



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Nigel,

I am using XL2003. I was testing it on a spreadsheet sent to me by someone
else, so wasn't sure if this would have caused a problem if their version was
older. So I have tested it on one of my spreadsheets with the same error.

Regards,

Richard

"Nigel" wrote:

Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Richard,

Your amended code line:

Set Rng = ActiveSheet.UsedRange 'SH.UsedRange

should read:

Set Rng = SH.UsedRange

as per my suggested code.

However, if you wish, you may send me your workbook:


norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )


---
Regards,
Norman


"Richard" wrote in message
...
Hi Norman,

I did change the code, I also altered the rows to the following in case I
had inserted a typo:

'Set WB = Workbooks("6036 Pay Cert.xls") '<<===== CHANGE
'Set SH = WB.Sheets("6") '<<===== CHANGE
Set Rng = ActiveSheet.UsedRange 'SH.UsedRange

The same thing happened.

I have also manually deleted contents from a few blank rows, to ensure
that
the rows are blank. Again, the same thing happened.

The cells have borders, but I have assumed this should not affect it.

delete all blank rows in a spreadsheet
 

Nigel,

I have changed the row of code causing the error to:

For xr = Val(StrReverse(ActiveSheet.UsedRange.Address)) To 1 Step -1

The code now works.

Thanks for your help.

"Nigel" wrote:

Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Well that is strange it works fine on my xl2003.

What error message do you get?



--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

I am using XL2003. I was testing it on a spreadsheet sent to me by someone
else, so wasn't sure if this would have caused a problem if their version
was
older. So I have tested it on one of my spreadsheets with the same error.

Regards,

Richard

"Nigel" wrote:

Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Ah - you are storing the code in your workbook or standard module not the
worksheet it is intended to act upon. Your addition of an explicit
reference is a good idea and makes it more general. Glad it now works. HTH

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

I have changed the row of code causing the error to:

For xr = Val(StrReverse(ActiveSheet.UsedRange.Address)) To 1 Step -1

The code now works.

Thanks for your help.

"Nigel" wrote:

Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Mike
Typo from me, I added the strReverse function, as this code was originally
written for xl97 and could not use this function, the last numerical value
(the last row in the used range) did not get reversed. I should have
changed it to the following with explicit reference to the active sheet...
Thanks also for the advice that it works in xl2000 (I have no experience of
that version).

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1


--
Cheers
Nigel



"Mike Fogleman" wrote in message
...
Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code
would begin on row 82 and work upwards. That scenario would work. If the
last row was 82 then the code would begin on 28 and miss all the rows
between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Richard
Mike Fogle pointed out an error in the creation of the last used row
reference, see later his post and my reply that follows for explanation

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

I have changed the row of code causing the error to:

For xr = Val(StrReverse(ActiveSheet.UsedRange.Address)) To 1 Step -1

The code now works.

Thanks for your help.

"Nigel" wrote:

Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may
be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Mike,

I haven't tested it with your example (i.e. row 82), but I changed the
relevant code to:

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1

which I think should overcome the problem.

"Mike Fogleman" wrote:

Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Norman,

Thanks for your help. I have managed to get the code that Nigel provided to
work.

But thanks for your efforts.

"Norman Jones" wrote:

Hi Richard,

Your amended code line:

Set Rng = ActiveSheet.UsedRange 'SH.UsedRange

should read:

Set Rng = SH.UsedRange

as per my suggested code.

However, if you wish, you may send me your workbook:


norman_jones@NOSPAMbtconnectDOTcom

(Delete "NOSPAM" and replace "DOT" with a full stop [period] )


---
Regards,
Norman


"Richard" wrote in message
...
Hi Norman,

I did change the code, I also altered the rows to the following in case I
had inserted a typo:

'Set WB = Workbooks("6036 Pay Cert.xls") '<<===== CHANGE
'Set SH = WB.Sheets("6") '<<===== CHANGE
Set Rng = ActiveSheet.UsedRange 'SH.UsedRange

The same thing happened.

I have also manually deleted contents from a few blank rows, to ensure
that
the rows are blank. Again, the same thing happened.

The cells have borders, but I have assumed this should not affect it.

delete all blank rows in a spreadsheet
 

String operations can be expensive, you might want to try a more conventional
approach:

Sub Tester()
Dim Start As Single
Dim num As Long, rw As Long
Dim rng as Range
num = 25000
Start = Timer
Set rng = ActiveSheet.UsedRange
For i = 1 To num
rw = rng(rng.Count).Row
Next
Debug.Print Timer - Start

Start = Timer
For i = 1 To num
rw = StrReverse(Val(StrReverse( _
ActiveSheet.UsedRange.Address)))
Next
Debug.Print Timer - Start


End Sub

I get the string approach as taking about 3 times as long.

For one command, obviously not a biggy, but no use getting into bad habits.

--
Regards,
Tom Ogilvy

"Richard" wrote:

Mike,

I haven't tested it with your example (i.e. row 82), but I changed the
relevant code to:

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1

which I think should overcome the problem.

"Mike Fogleman" wrote:

Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Hi Tom,

I am not as 'fluent' as you guys with VBA. What part of the code does your
suggested code replace? How do I fit into the 'delete blank rows' code?

Cheers,

Richard

"Tom Ogilvy" wrote:

String operations can be expensive, you might want to try a more conventional
approach:

Sub Tester()
Dim Start As Single
Dim num As Long, rw As Long
Dim rng as Range
num = 25000
Start = Timer
Set rng = ActiveSheet.UsedRange
For i = 1 To num
rw = rng(rng.Count).Row
Next
Debug.Print Timer - Start

Start = Timer
For i = 1 To num
rw = StrReverse(Val(StrReverse( _
ActiveSheet.UsedRange.Address)))
Next
Debug.Print Timer - Start


End Sub

I get the string approach as taking about 3 times as long.

For one command, obviously not a biggy, but no use getting into bad habits.

--
Regards,
Tom Ogilvy

"Richard" wrote:

Mike,

I haven't tested it with your example (i.e. row 82), but I changed the
relevant code to:

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1

which I think should overcome the problem.

"Mike Fogleman" wrote:

Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long, rng as Range

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

set rng = Activesheet.UsedRange
For xr = rng(rng.count).row To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub

--
Regards,
Tom Ogilvy


"Richard" wrote:

Hi Tom,

I am not as 'fluent' as you guys with VBA. What part of the code does your
suggested code replace? How do I fit into the 'delete blank rows' code?

Cheers,

Richard

"Tom Ogilvy" wrote:

String operations can be expensive, you might want to try a more conventional
approach:

Sub Tester()
Dim Start As Single
Dim num As Long, rw As Long
Dim rng as Range
num = 25000
Start = Timer
Set rng = ActiveSheet.UsedRange
For i = 1 To num
rw = rng(rng.Count).Row
Next
Debug.Print Timer - Start

Start = Timer
For i = 1 To num
rw = StrReverse(Val(StrReverse( _
ActiveSheet.UsedRange.Address)))
Next
Debug.Print Timer - Start


End Sub

I get the string approach as taking about 3 times as long.

For one command, obviously not a biggy, but no use getting into bad habits.

--
Regards,
Tom Ogilvy

"Richard" wrote:

Mike,

I haven't tested it with your example (i.e. row 82), but I changed the
relevant code to:

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1

which I think should overcome the problem.

"Mike Fogleman" wrote:

Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

Thanks, Tom.

Works a treat.

"Tom Ogilvy" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long, rng as Range

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

set rng = Activesheet.UsedRange
For xr = rng(rng.count).row To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub

--
Regards,
Tom Ogilvy


"Richard" wrote:

Hi Tom,

I am not as 'fluent' as you guys with VBA. What part of the code does your
suggested code replace? How do I fit into the 'delete blank rows' code?

Cheers,

Richard

"Tom Ogilvy" wrote:

String operations can be expensive, you might want to try a more conventional
approach:

Sub Tester()
Dim Start As Single
Dim num As Long, rw As Long
Dim rng as Range
num = 25000
Start = Timer
Set rng = ActiveSheet.UsedRange
For i = 1 To num
rw = rng(rng.Count).Row
Next
Debug.Print Timer - Start

Start = Timer
For i = 1 To num
rw = StrReverse(Val(StrReverse( _
ActiveSheet.UsedRange.Address)))
Next
Debug.Print Timer - Start


End Sub

I get the string approach as taking about 3 times as long.

For one command, obviously not a biggy, but no use getting into bad habits.

--
Regards,
Tom Ogilvy

"Richard" wrote:

Mike,

I haven't tested it with your example (i.e. row 82), but I changed the
relevant code to:

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1

which I think should overcome the problem.

"Mike Fogleman" wrote:

Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to why
reverse the row number? If the UsedRange ended at row 28 then the code would
begin on row 82 and work upwards. That scenario would work. If the last row
was 82 then the code would begin on 28 and miss all the rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

The old double-reverse trick. You must like football!
"Nigel" wrote in message
...
Hi Mike
Typo from me, I added the strReverse function, as this code was originally
written for xl97 and could not use this function, the last numerical value
(the last row in the used range) did not get reversed. I should have
changed it to the following with explicit reference to the active sheet...
Thanks also for the advice that it works in xl2000 (I have no experience
of that version).

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1


--
Cheers
Nigel



"Mike Fogleman" wrote in message
...
Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to
why reverse the row number? If the UsedRange ended at row 28 then the
code would begin on row 82 and work upwards. That scenario would work. If
the last row was 82 then the code would begin on 28 and miss all the rows
between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A, there
may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.

delete all blank rows in a spreadsheet
 

World Cup is coming!!

--
Cheers
Nigel



"Mike Fogleman" wrote in message
...
The old double-reverse trick. You must like football!
"Nigel" wrote in message
...
Hi Mike
Typo from me, I added the strReverse function, as this code was
originally written for xl97 and could not use this function, the last
numerical value (the last row in the used range) did not get reversed. I
should have changed it to the following with explicit reference to the
active sheet... Thanks also for the advice that it works in xl2000 (I
have no experience of that version).

For xr = StrReverse(Val(StrReverse(ActiveSheet.UsedRange.Ad dress))) To 1
Step -1


--
Cheers
Nigel



"Mike Fogleman" wrote in message
...
Nigel, StrReverse worked in XL2000 but UsedRange failed. Added
(Sheet1.UsedRange.Address) and it worked. However I was confused as to
why reverse the row number? If the UsedRange ended at row 28 then the
code would begin on row 82 and work upwards. That scenario would work.
If the last row was 82 then the code would begin on 28 and miss all the
rows between.

Mike F
"Nigel" wrote in message
...
Are you using xl2002 or xl2003, the strReverse was not valid in prior
versions?

--
Cheers
Nigel



"Richard" wrote in message
...
Nigel,

Thanks. When I ran the code an error was thown up. The debugger
highlighted
this row

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1

Should I have selected the used range?

"Nigel" wrote:

Sub RemoveEmptyRows()

Dim xr As Long, xc As Integer, dRow As Boolean
Dim CalcType As Long

With Application
.ScreenUpdating = False
CalcType = .Calculation
.Calculation = xlCalculationManual

End With

For xr = Val(StrReverse(UsedRange.Address)) To 1 Step -1
dRow = True
For xc = 1 To 255
If Len(Trim(Cells(xr, xc))) 0 Then
dRow = False
Exit For
End If
Next xc
If dRow Then Rows(xr).Delete shift:=xlUp
Next xr

With Application
.ScreenUpdating = True
.Calculation = CalcType
End With
End Sub


--
Cheers
Nigel



"Richard" wrote in message
...
How do I delete entire rows in a spreadsheet with a VBA macro?

I can't use autofilter (or similar techniques) as not all of the
columns
have data in every non blank row i.e. if I sorted by column A,
there may
be a
row with a blank cell in column A, but another column (say AX) that
may be
out of view could have data in it.