Deleting Blank Rows at end of sheet

[RLN]

RE: Excel 2003 SP3 / WinXP SP3

Some Excel sheets I recieve from other departments in house have blank
rows at the end of their actual data.
I had a sheet recently that had 35 rows of actual data. It bombed on
a blank row when imported to another system. I did <ctrl><home> then
<ctrl><end> on the sheet and found I was sitting on row 359!

Below is a routine I came up with to delete all blank rows below the
last valid data row. It takes about 15 seconds to run. Always
seeking more efficient ways to do stuff, I know there are some Excel
MVP's out here. So if there is a better/faster/leaner way to
accomplish this task than what I have posted here, I would welcome the
solution.

Sub DeleteBlankRowsOnly()
'delete all blank rows below the last valid row.
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

'65536 row limit in Excel2003...
For row_index = 65537 - 1 To 1 Step -1
If Cells(row_index, 1).Value = "" Then
Rows(row_index).Delete
End If
Next
Application.ScreenUpdating = True
ActiveWorkbook.Save
End Sub


Thanks.

 

[Rick Rothstein]

The problem is with the UsedRange that Excel tracks... under certain
conditions, it doesn't get updated to reflect the actually used range of
data. I think this macro will fix the problem you are having (provided your
data never goes down to the very last row on the worksheet)...

Sub FixUsedRange()
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
.Cells(LastRow + 1, 1).Value = "X"
.Range(.Rows(LastRow + 1), .Rows(.UsedRange.Rows.Count)).Delete
End With
End Sub

 

[RLN]

Rick,
I tried this code here you provided and it is not doing what I thought
it would do.
My sheet has 35 rows of valid data (this row qty can vary from month
to month)
In this particular case, a <ctrl+home>, <ctrl+end> revealed this sheet
has not 35 rows, but 350 rows. Rows 36-350 are completely blank and
contain nothing. Rows 36-350 are the rows I'm trying to delete (then
do a save afterwards).
As I stepped through your code with the debugger, all I saw it really
do was place an "X" in column 1 of row 36, then delete row 36.
Is there is something else I might be missing here?

 

[Rick Rothstein]

Are you saying that after running the code I gave you, hitting Ctrl+End
still takes you to Row 350? If so, then something else may be going on... if
you want, you can send the workbook to me and I'll look at what you actually
have (remove the NO.SPAM stuff from my posted email address).

Just so you know, my code adds an "X" to the row after the last piece of
data (or formula) so that Excel has something to delete... just deleting
what Excel thinks are already blank rows does not readjust the UsedRange...
it seems Excel must actually have something to delete before it will adjust
the UsedRange. This line...

..Range(.Rows(LastRow + 1), .Rows(.UsedRange.Rows.Count)).Delete

deletes the row I put the "X" in down to the last row Excel "thinks" is in
use... it doesn't just delete the cell I added the "X" to.

--
Rick (MVP - Excel)


Rick,
I tried this code here you provided and it is not doing what I thought
it would do.
My sheet has 35 rows of valid data (this row qty can vary from month
to month)
In this particular case, a <ctrl+home>, <ctrl+end> revealed this sheet
has not 35 rows, but 350 rows. Rows 36-350 are completely blank and
contain nothing. Rows 36-350 are the rows I'm trying to delete (then
do a save afterwards).
As I stepped through your code with the debugger, all I saw it really
do was place an "X" in column 1 of row 36, then delete row 36.
Is there is something else I might be missing here?

 

[Jeff.Gervais]

I was having this problem as well. I found this works but I am unsure why.

In the affected spread sheet hit CTRL+END which takes us down past a bunch
of blank rows.
Now go to the first blank row past all the good data in column A, hold
CTRL+shift+END then edit, delete, shift cells up (or left doesn't matter)
Try the CTRL+END again. We still go past the blank rows. (You would think
this would have fixed it but it hasn't.)
CTRL+HOME to the top of the sheet.

NOW, create this macro and run it.

Sub test()
Dim myRows As Long
myRows = ActiveSheet.UsedRange.Rows.Count
MsgBox (myRows)
End Sub

All it does is access UsedRange and display the count, but it displays the
correct row at the end of the good data range.

Try CTRL+END again and you get to the end of the good data without going
past all the blank rows.

Somehow accessing UsedRange made it update.

 

[Gord Dibben]

Try the CTRL+END again. We still go past the blank rows. (You would think

this would have fixed it but it hasn't.)

Jeff

After deleting the unused rows and columns...........SAVE the workbook and
CTRL + END will be reset.

In some older versions you had to save then close and re-open to reset.

No need to run any macro to reset the usedrange.


Gord Dibben MS Excel MVP

 

[RLN60]

I was going through some posts and realized I had not responded back...I am
very sorry...not intentional.

I was going the macro route here, and realized a simple save and reopen
fixed it.

RLN