Reformatting Database

[Snapshot84]

Hi,

I have a database i've been working with and today I went to print the sheet and noticed that the sheet had many many blank rows. So when I went to print the table would not fit to the page correctly. Is there a way to re-format a sheet so that it eliminates all the extra blank rows?? I can scroll down very far (row 65407) when I only have 75 rows of data. Please help

 

[Hermanito]

Are these 75 rows of data also the first 75 rows in the worksheet, or are they spread out?
Resetting the UsedRange (which is used to determine what is printed) can easily be done in the VBA-editor, not in Excel itself as far as I know.
Rightclick on the tab of the sheet you want to print and select View Code. You are now in the VBA-Editor. Press CTRL+G to make sure the immediate window is shown, then enter this in the Immediate Window and press Enter behind it.

?activesheet.usedrange.address

It will show a range, but just executing this statement will also have forced Excel to reset it's UsedRange. Go back to Excel, and check what the new last cell in the UsedRange is by pressing CTRL+End. That should now be the last used cell in the last row of those 75 datarows.

 

[Snapshot84]

Hello, thanks for the help. The 75 rows are the first 75 rows. I tried resetting the rows and I got

"$A$1:$S$44247"

So it reduced it to 44247 rows....? I dont have any data in any rows below 75. Any ideas?

 

[Hermanito]

I guess it did reset it only to 44247, probably because there is some kind of data there. Maybe some cell formatting?
You could try selecting the row just below the 75th datarow (select the full row, by clicking on the rownumber) then press and HOLD Shift, then press End, then press CursorDown, then release Shift. Now you selected ALL rows below your data. Rightclick anywhere in the selection and choose 'Delete'.
Now try the UsedRange thing again... it should have reset better now...

 

[Snapshot84]

There we go, that reset much better.

What causes that to happen and are there settings I can enable or disable to prevent it? I think it was probably formatting since i never entered any data. I may have highlighted an entire column at one point and added formatting. Are there settings that you are aware of to make it just format the table rows/columns?

I have some formatting in my rows that does expand past the table (it doesnt mess up the scrolling) but it looks messy. Is there a similar code to reset the rows?

 

[Hermanito]

Try to avoid applying cell formatting to full columns, that happens to mess up the way Excel tries to define the last used cell.
When deleting rows like I explained in my previous message, you do more than deleting the contents (pressing the Delete key is not the same action as rightclicking and choosing Delete!!), the rows are actually deleted and replaced with fresh new ones, that don't have any formatting applied to them at all.
Unfortunately there are no settings to avoid Excel from getting confused

I assume you mean a similar way to reset columns? To select all columns next to your data, do this: select the first empty column to the right your data by clicking the columnheader, then hold Shift, click End, CursorRight, release Shift, rightclick Delete...

 

[Snapshot84]

Gotcha. Thank you for the help!