Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet format question (Excel 2000)

    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

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

    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. <pre>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</pre>

    HTH
    Gre

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

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

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

    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.
    Gre

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

    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

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

    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
    Gre

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Location
    Illinois, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

    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.

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

    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

  9. #9
    Star Lounger
    Join Date
    Jan 2001
    Location
    Charlottetown, Prince Edward Island, Canada
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet format question (Excel 2000)

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

    Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •