Deleting blank rows at the end of the sheet!

  • Thread starter Thread starter jaclyn
  • Start date Start date
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?
 
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
 
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.
 
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...
 
Back
Top