LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 8
Default Deleting Blank Rows at end of sheet

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.
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help Deleting Blank Rows JCG Excel Worksheet Functions 3 February 22nd 10 04:53 PM
Deleting blank rows which contain blank drop-down list boxes Al Excel Programming 3 February 18th 09 01:37 PM
deleting the blank rows from the sheet manu Excel Worksheet Functions 4 July 9th 07 09:29 PM
Deleting blank rows Yvette Excel Worksheet Functions 3 June 9th 06 10:30 AM
deleting blank rows Ryan H. Excel Programming 2 August 3rd 04 03:27 AM


All times are GMT +1. The time now is 05:42 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"