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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.