Worksheet format question (Excel 2000)

Topic

New Reply

I have an Excel spreadsheet, there are 44 active rows within this sheet. When i print it prints 44 rows only but when i view the worksheet on line, the scroll bar indicates a lot more rows…..about 64800 rows….. Is there something the has caused my worksheet to go from 1 to infinity and is there a way i can reset the size?

Thanks

Reply | Quote

Replies

If what you’re stating is that the remaining 64,756 rows are empty, you could try running this code – which was written by one of the other WMVPs. It should remove any stray “contents” from empty cells.

Sub ResetUsedRange() 'by Andrew Cronnolly
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim sht As Integer
    sht = ActiveSheet.Index
    For Each ws In ActiveWorkbook.Worksheets
        ws.Activate
        ActiveSheet.UsedRange
    Next
    ActiveWorkbook.Worksheets(sht).Activate
    Application.ScreenUpdating = True
End Sub

HTH

Reply | Quote

Yes, the remaining rows are empty. Do i cut and paste this into a VB editor window and run it?

Reply | Quote

Yes. You can either insert it into your personal.xls – if you have one – or insert a Module into the current workbook, paste it in there, position the cursor inside the block of code and press F5 to run it.

Reply | Quote

i cut and pasted it into a VB module and then ran it. Nothing happened unfortunately…it doesn’t seem to run at all. I have attached the document in question and any help would be appreciated.

Thanks

Reply | Quote

It looks like your workbook has become partly corrupted. Since there is not a great deal of data in it, I would suggest that you rebuild it. Other than the Column Widths, everything can be migrated using Paste Special. HTH

Reply | Quote

I cut and Pasted the values over to a new worksheet, it looks fine now.

Thanks for the help!

Reply | Quote

Try this: put your cursor in cell A45 (the cell following the last populated row), then Ctrl+Shft+End (which selects everything from that point to the end of the worksheet). Now delete the highlighted rows. That should solve your problem.

Reply | Quote

I tried this suggestion and no luck. When i go to the A45 cell and do the ctrl-shift-end, it highlights the range of cells, i hit right mouse button, select Delete Row, and nothing changes.

Thanks

Reply | Quote