Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Need Help Deleting Blank Rows | Excel Worksheet Functions | |||
Deleting blank rows which contain blank drop-down list boxes | Excel Programming | |||
deleting the blank rows from the sheet | Excel Worksheet Functions | |||
Deleting blank rows | Excel Worksheet Functions | |||
deleting blank rows | Excel Programming |