EXCEL: Why 65k Rows? How to Reset Vert Scroll?

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
EXCEL

I have a workbook where I am trying to format the sheets with VBA so that they will print correctly. The idiots that edit the sheets have found innumerable ways to foul up my program. The latest one is nasty ... they have done something that makes the "UsedRange" function return 65533 rows. I don't know why it doesn't include the bottom 3 rows??? You can tell by the miniscule size of the vertical scroll button that a LOT of rows are active!

Anyway, I have looked at the cells and there doesn't seem to be any reason at all that the cells below the REAL actual used range should be considered active, but they definitely are. Is there any way to reset the Used Range? Does anyone have any idea as to what formatting (or other?) function drives the Used Range to the bottom of the sheet?

Any assistance in this inquiry is sincerely appreciated.

[D-niss]

This is prolly not the best way to do it but, I've you tried deleting all the rows from the last data line right to the bottom of the sheet?

[Webtest]

Thanks D-niss ... nice try ...

You can only delete as many rows as you have INactive rows. If you try to delete 65,000 rows, it crashes memory and gets really nasty.

I did a CountA function on a bunch of rows and they all returned "0" indicating that all the cells are really empty below the last active row (328).

I guess I'm just going to have to lock down the formatting for the sheet, but I'm sure the idiots out there will still find some way to break it.

[Webtest]

Incidentally, I do have the "ActualUsedRange" function that I found on the Web, but it broke because my integer variable isn't capable of a value of 65,000. I'd hate to have to scan 65000 rows to get back down to the 300+ that are actually good.

[D-niss]

Thanks D-niss ... nice try ...

You can only delete as many rows as you have INactive rows. If you try to delete 65,000 rows, it crashes memory and gets really nasty.

I did a CountA function on a bunch of rows and they all returned "0" indicating that all the cells are really empty below the last active row (328).

I guess I'm just going to have to lock down the formatting for the sheet, but I'm sure the idiots out there will still find some way to break it.

I'm sorry perhaps I did not really understand what you meant. Can you be a little more specific?

Do you want to do this by code or once manually on a sheet? I can select 65000 rows manually and delete them all is .02 seconds no prblms I've seen this sometime on older version when user has formatted say a whole column.

I usually set up a range named Print_Area in advance and modify it by code to adjust for change in the sheet. Is that what you meant?

I can post example of the code if you want.

Regards