Worksheet vertical scrollbar too long

[PATSYS]

Hi all,

I have this file (with one worksheet in it) created in excel 2003.

I am now using excel 2007 and this worksheet only has data up to 903 rows. However the veritical scroll is too long.

I tried going to row 904, CTRL+SHIFT+END then delete entire row. After saving, it is still the same, scrollbar still too long.

Another weird about this worksheet it is that it has up to column XFD, but row is only up to 65536. So I am confused if this is excel 2003 worksheet or 2007.

Lastly, everytime I delete a column, the new column that comes out from the end (XFD) automatically gets a shade.

Anybody experienced this?

 

[pboltonchina]

Try running this

Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
  With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
Next wks
End Sub

 

[xenou]

My first thought was copy the data into a new workbook - and cross fingers. You'd want to copy only the used range of cells with data, not the entire sheet, and if possible as little of the information as needed to preserve the fundamental data you need. If it's just some wierd bytes stuck somewhere maybe you'll shake it off. Also get out of compatibility mode if you can.

 

[PATSYS]

Try running this

Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range

For Each wks In ActiveWorkbook.Worksheets
  With wks
    myLastRow = 0
    myLastCol = 0
    Set dummyRng = .UsedRange
    On Error Resume Next
    myLastRow = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByRows).Row
    myLastCol = _
      .Cells.Find("*", after:=.Cells(1), _
        LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchDirection:=xlPrevious, _
        SearchOrder:=xlByColumns).Column
    On Error GoTo 0

    If myLastRow * myLastCol = 0 Then
        .Columns.Delete
    Else
        .Range(.Cells(myLastRow + 1, 1), _
          .Cells(.Rows.Count, 1)).EntireRow.Delete
        .Range(.Cells(1, myLastCol + 1), _
          .Cells(1, .Columns.Count)).EntireColumn.Delete
    End If
  End With
Next wks
End Sub

Hi pboltonchina,

Thanks for the codes but that didn't solve it.

 

[PATSYS]

My first thought was copy the data into a new workbook - and cross fingers. You'd want to copy only the used range of cells with data, not the entire sheet, and if possible as little of the information as needed to preserve the fundamental data you need. If it's just some wierd bytes stuck somewhere maybe you'll shake it off. Also get out of compatibility mode if you can.

Hi Xenou,

That did it. I just copied to a new sheet.

It must have something to do with this compatibility mode.

Thanks