Reset last cell

[Nanogirl21]

I am using the below code to delete all blank rows/columns and then fill all remaining blank cells or cells with value "(blank)" with a dash (-).
Is there anything that I can add to reset excels last cell after the extra columns and rows have been deleted?
In the past i've used ASAP Utilities, but I am trying to get away from add-ons.

Dim ws As Worksheet

For Each ws In Worksheets
    
    On Error Resume Next
    ActiveSheet.UsedRange
    ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    ws.Cells.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
    ws.Cells.SpecialCells(xlBlanks).Value = "-"
    
    Cells.Replace What:="(blank)", Replacement:="-", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    On Error GoTo 0
Next ws

Thank you

 

[decadence]

Hi, Yes there is as I had the same problem all you need to do is add this to the end of your code

    [COLOR="#0000CD"]ActiveSheet.UsedRange[/COLOR]

 

[Yongle]

ActiveSheet.UsedRange is not always reliable
- non-cleared formatting (eg BOLD) can cause the wrong range to be returned

Find a way to determine the last column with value and the last row with value and delete all columns and rows beyond those
eg macro below uses last cell with value in column A and in row 1

Sub ResetLastCell()
    Dim lc As Long, lr As Long
    With ActiveSheet
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
    
        .Rows(lr + 1).Resize(Rows.Count - lr).Delete
        .Columns(lc + 1).Resize(, Columns.Count - lc).Delete
    End With
End Sub

 

[Yongle]

and this excellent page from Ron de Bruin is worth a read

 

[decadence]

Hi Yongle, You do have a good point, however the case is that this will not reset the true last cell. The original used range will still be there even after deleting all the empty columns, they will just be hidden.

Lets say you have 10 rows and 10 columns and however many rows and columns are empty/blank i.e Columns B:C are empty/blank and Row 5 is empty, then delete the empty/blank Rows and Columns which should leave you with 8 columns by 9 rows of data, however the true range is still 10 by 10, the codes below will show you the true range even after deleting all empty/blank rows and columns.

    ActiveSheet.range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
'Or
    ActiveSheet.range("A1", ActiveCell.SpecialCells(xlLastCell)).Copy

If you use the Active sheet used range code then try the codes above again you will get the true range to be 8 by 9

 

[decadence]

Nanogirl21 Try this...

Dim ws As Worksheet

For Each ws In Worksheets
    
    On Error Resume Next
    ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete  ' Delete Rows
    ws.Cells.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete  ' Delete Columns
    ActiveSheet.UsedRange  '  Reset the last true excel cell
    ws.Cells.SpecialCells(xlBlanks).Value = "-"  ' Adds a dash to any blank cells in the new true range of cells
    On Error GoTo 0
Next ws

 

[Nanogirl21]

Nanogirl21 Try this...

Dim ws As Worksheet

For Each ws In Worksheets
    
    On Error Resume Next
    ws.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete  ' Delete Rows
    ws.Cells.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete  ' Delete Columns
    ActiveSheet.UsedRange  '  Reset the last true excel cell
    ws.Cells.SpecialCells(xlBlanks).Value = "-"  ' Adds a dash to any blank cells in the new true range of cells
    On Error GoTo 0
Next ws

worked perfect. thanks.