delete all blank rows in a spreadsheet

[Guest]

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.

 

[Guest]

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

 

[Norman Jones]

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
'<<================

 

[Nigel]

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

 

[Guest]

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.

 

[Guest]

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)

 

[Guest]

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?

 

[Norman Jones]

Hi Richard,

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

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?

 

[Guest]

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.

 

[Nigel]

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

 

[Guest]

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

 

[Norman Jones]

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] )

 

[Guest]

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]

Well that is strange it works fine on my xl2003.

What error message do you get?

 

[Mike Fogleman]

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]

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

 

[Nigel]

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.Address))) To 1
Step -1

 

[Nigel]

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

 

[Guest]

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.Address))) To 1
Step -1

which I think should overcome the problem.

 

[Guest]

Hi Norman,

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

But thanks for your efforts.

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

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.