can't delete rows?!?

[katzav]

Hi,

In many cases when I get a file from someone else and it is unreasonably big (in size), I check whether or not there are worksheet with extra lines being formatted....which cause the file to be big. then I mark the rows / columns and right click delete them. That usually will re size the scroll bar to the right size according to the actual content I have. today I got a 14MB file and manage to reduce it to 4.5 MB using the same technique. However there is one worksheet that the scroll-bar shows there are 2000 rows when there are only 600 in use. I tried to delete the rows but nothing is changed.

Please help? one hypotasis that I have is that maybe other worksheets has formula pointing to those rows like Sum('worksheet name'!$A$4:$A$2000) but this is just an Hypotasis.

Appreciate your help,
IK

 

[pboltonchina]

When I've had problems like this I select my last used cell, then press Ctrl+Alt+End and this selcts down and across to the last used cell then clear contents, fill and borders. Then run this code.

Sub DeleteUnused()
  
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub

 

[katzav]

I found the issue, comments have been shifted and are now located on the row below the table instead of next to the cell. and this is the reason why I can't delete the rows.

I will start a new thread on how to solve this.

Thanks for your help,
Isaac