Used Range won't reset

[njimack]

I have a particular worksheet in a file for which the used range seems to be stuck at 256 columns, but I know that the last used column is AV.

Using the Name Manager from www.jkp-ads.com, I've confirmed that there aren't any named ranges that intersect columns AW onwards, yet when I try to reset the used range using ASAP Utilities, the used range is still 256 columns.

Any ideas?

Thanks

 

[Datsmart]

This macro from David McRitchie will make the ActiveCell the last cell. Select the cell you want, then run the macro.
I have used it many times with no problems.

Sub makelastcell()
'David McRitchie, http://www.mvps.org/dmcritchie/excel/lastcell.htm
Dim x As Integer
Dim str As String
Dim xlong As Long, clong As Long, rlong As Long
On Error GoTo 0
x = MsgBox("Do you want the activecell to become " & _
"the lastcell" & Chr(10) & Chr(10) & _
"Press OK to Eliminate all cells beyond " _
& ActiveCell.Address(0, 0) & Chr(10) & _
"Press CANCEL to leave sheet as it is", _
vbOKCancel + vbCritical + vbDefaultButton2)
If x = vbCancel Then Exit Sub
str = ActiveCell.Address
Range(ActiveCell.Row + 1 & ":" & Cells.Rows.Count).Delete
xlong = ActiveSheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73
'use of filters can interfer with column elimination
Range(Cells(1, ActiveCell.Column + 1), _
Cells(Cells.Rows.Count, Cells.Columns.Count)).Delete
Beep
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
ActiveWorkbook.Save
xlong = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Columns.Count 'Tip73
rlong = Cells.SpecialCells(xlLastCell).Row
clong = Cells.SpecialCells(xlLastCell).Column
If rlong <= ActiveCell.Row And clong <= ActiveCell.Column Then Exit Sub
MsgBox "Sorry, Have failed to make " & str & " your last cell"
End Sub

This macro is set up to delete all rows and columns past the active cell, not just the rows and columns past the active cell but limited to the used areas. This appears to be the only way to guarantee the active cell becomes the last cell.
Color formatting that commences outside the new used area will be lost. Color formatting that starts within the new used area will extend beyond the used area.
All number formatting (includes characters) outside of the new used area will be destroyed no matter the point of origin.
If the resulting last cell remains beyond the active cell, it will be noted as an error. If the active cell is beyond the resulting last cell that is acceptable, and no data should have been removed before the active cell.

 

[njimack]

Brilliant.

Many thanks

 

[shippey121]

another way to reset the last cell, is like mention delete all rows and coloums that are not used then select cell A1 and save, cant remeber where i found this out but in older versions of excel to reset the last used cell the active cell had to be in A1 when saved


HTH

 

[CVDK]

What have I done wrong? The above macro does not work (it conludes that it's unable to make the selected cell my last cell.

Further I've tried deleting unused rows and columns, activating A1 and saving the workbook without any luck.

I also tried the simple usedrange macro and still no luck.

Also tried deleting all named ranges before doing any of the above.

Any ideas?

PS: Apart from the above it's a workbook with no macros.

 

[Sektor]

Sub DeleteUnusedRange()

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

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

    If lRealLastRow < lLastRow Then
        Range(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.Delete
    End If
    
    If lRealLastColumn < lLastColumn Then
        Range(Cells(1, lRealLastColumn + 1), Cells(1, lLastColumn)).EntireColumn.Delete
    End If
    
    ActiveSheet.UsedRange

End Sub

 

[CVDK]

Thank you, but it doesn't work on my sheet.

So far I've just moved everything to a new sheet and started over. Since this happens now and then in Excel, I'd still appreciate it if anyone can unlock the secret.