Deleting blank rows at the end of the sheet!

J

jaclyn

I've tried it and it's not working.
My empty cells is all the way to the maximum amount of
rows...65000 something.

Know of any other way to get rid of these rows?
 
R

Ron de Bruin

You must also close the workbook and reopen jaclyn.(sorry I forgot to say)
 
J

JMay

This is a great (workhorse-type) macro and does a great job. In my
workplace I can only wish that users prior to me (who pass on to me
worksheets that they have been "screwing-around-with) ,, well anyway when I
do a Control-End upon opening such a workbook the active cell becomes
"CZ32586". Using the current macro I could run it 1+infinity as the users
sometime enter " " (that's five space characters) in 1,185 cells
upward and to the left of the original CZ32586. So there must be a "MORE
ROBUST" Clean up of "soiled (like in diapers) -sheets"; Is there one?
Thanks for your tremendous assistance to us "youngsters in the trade"...
JMay
 
D

Dave Peterson

That macro will try to clean up the usedrange when there really isn't anything
in the cell. Like it's been used, and then cleared.

But in your case with the spaces in there, you could clean them up and then run
the other macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
myCell.ClearContents
End If
Next myCell
End If
End With

End Sub

But as much as I hate seeing people type a spacebar into a cell (to clear it??),
I'd be careful. Maybe there's a reason that they did it.
 
J

JMay

Thanks Dave; You are Right-On...
JMay

Dave Peterson said:
That macro will try to clean up the usedrange when there really isn't anything
in the cell. Like it's been used, and then cleared.

But in your case with the spaces in there, you could clean them up and then run
the other macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

With ActiveSheet
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
myCell.ClearContents
End If
Next myCell
End If
End With

End Sub

But as much as I hate seeing people type a spacebar into a cell (to clear it??),
I'd be careful. Maybe there's a reason that they did it.

On the other hand, if I inherit a workbook that's filled with them, I clean them
with no compunction. (ooh. Compunction.)
Click to expand...
 
Top