Reduce file size..

[Mashtonsmith]

Hi all,

I'm sure this has been asked many times so apologies.

I have a worksheet where data ends at row 49301 and column CQ - yet when I hit CRTL END I'm taken to Row 49,307 and Column WYY.

I have cleared contents AND deleted columns CR - WYY but it's still going to the same position.

What else can I do?

I'm using Excel 2007.

 

[Sektor]

Run this procedure.

[COLOR="Blue"]Sub[/COLOR] DeleteUnusedRange()

    [COLOR="Blue"]Dim[/COLOR] lLastRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], lLastColumn [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] lRealLastRow [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR], lRealLastColumn [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    
    [COLOR="Blue"]With[/COLOR] Range("A1").SpecialCells(xlCellTypeLastCell)
        lLastRow = .Row
        lLastColumn = .Column
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]

    lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
    lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).Column

    [COLOR="Blue"]If[/COLOR] lRealLastRow < lLastRow [COLOR="Blue"]Then[/COLOR]
        Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    
    [COLOR="Blue"]If[/COLOR] lRealLastColumn < lLastColumn [COLOR="Blue"]Then[/COLOR]
        Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)).EntireColumn.Delete
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
    
    ActiveSheet.UsedRange

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Jonmo1]

Hi all,

I'm sure this has been asked many times so apologies.

I have a worksheet where data ends at row 49301 and column CQ - yet when I hit CRTL END I'm taken to Row 49,307 and Column WYY.

I have cleared contents AND deleted columns CR - WYY but it's still going to the same position.

What else can I do?

I'm using Excel 2007.

Click Save.

It's a little quirk that Excel doesn't reset the LastCell untill the file is saved.


Also, make sure you actually Deleted the columns, not just cleared contents.
Are you pressing the Delete button on keyboard, or Right Click - Delete?
The delete key on keyboard only does "clear contents", doesn't delete the column..

 

[GlennUK]

Click Save.

It's a little quirk that Excel doesn't reset the LastCell untill the file is saved.

Also, make sure you actually Deleted the columns, not just cleared contents.
Are you pressing the Delete button on keyboard, or Right Click - Delete?
The delete key on keyboard only does "clear contents", doesn't delete the column..

You can make Excel reset it by macro, if you really need to:

Sub resetlastcell()
    dummy = ActiveSheet.UsedRange.Columns.Count
    dummy = ActiveSheet.UsedRange.Rows.Count
End Sub

 

[Jonmo1]

You can make Excel reset it by macro, if you really need to

Yep, you don't really need all that either...

This will suffice

Sub resetlastcell()
ActiveSheet.UsedRange
End Sub

 

[GlennUK]

Blimey, you learn something new every day! Thanks Jonmo.