how can i eliminate blank rows that exist beyond the last non-bank row?

[joe miller]

[h=2]my spreadsheet has more than a million blank rows after the last non-blank row.
how can i eliminate those blank rows?[/h]

 

[sheetspread]

Click on the first blank, ctrl-shift-down arrow, delete the rows.

 

[Rick Rothstein]

[h=2]my spreadsheet has more than a million blank rows after the last non-blank row.
how can i eliminate those blank rows?[/h]

You cannot "eliminate" them, but you can hide them from view using a macro...

[table="width: 500"]
[tr]
	[td]Sub HideRowsBelowLastVisibleData()
  Range(Rows(Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row + 1), Rows(Rows.Count)).Hidden = True
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (HideRowsBelowLastVisibleData) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jtakw]

Hi,

Cross posted here: https://www.excelforum.com/excel-ge...beyond-the-last-non-bank-row.html#post4910351

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

If you do cross-post in the future and also provide a link, then there shouldn’t be a problem though you may also want to consider whether cross-posting actually increases your chance of getting a quick answer. Some (but certainly not all) members, because of the point above, tend to skip questions that they know are cross-posted because they don’t want to bother going to the other forum(s) to check if it has already been solved.

 

[joe miller]

''Click on the first blank, ctrl-shift-down arrow, delete the rows'' didn't work
but ''Click on the first blank, ctrl-shift-down arrow, hide the rows'' did work.

thanks all you guys, you are a great resource!

how do i mark this thread ''answered''

 

[jproffer]

This particular board doesn't have the "answered" or "Solved" option.

 

[joe miller]

Home: Unfortunately, no Excel at home at this time

Work: XL13 on Win07

Any answer provided is based on this(these) condition(s). <------ ''i don't know what the red reply means??????

 

[MARK858]

Any answer provided is based on this(these) condition(s). <------ ''i don't know what the red reply means??????

What it states, jproffer has XL13 on Win07 and so uses the functions included in XL13/Win07 and so if you have another version (normally previous ones) then the answer might include functions not available in the version.

 

[jproffer]

Correct . Thanks Mark. That's a signature that goes into every post I put up...it really wasn't relevant this time, but it's not easily removed on a post-by-post basis either.

Sorry for any confusion.

EDIT: Actually, you did me a service though. I now have XL16, and had forgot to reflect that change in my signature. So thank you for that