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

joe miller

New Member
Joined
May 20, 2018
Messages
6
[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]
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
[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...
Code:
[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.
 
Upvote 0
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. :)
 
Upvote 0
''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''

 
Upvote 0
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??????
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top