Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Blank Rows at end of sheet
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 -- Rick (MVP - Excel) "RLN" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Blank Rows at end of sheet
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? On May 6, 12:29*pm, "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Blank Rows at end of sheet
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) "RLN" wrote in message ... 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? On May 6, 12:29 pm, "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Blank Rows at end of sheet
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Blank Rows at end of sheet
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 On Wed, 10 Jun 2009 13:23:01 -0700, Jeff.Gervais wrote: |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Blank Rows at end of sheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
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 |