Reset used range

[welshgasman]

Hi all,
I have a set of sheets, that I am trying to make an address book of.
In sheet A, the UsedRange is showing as K1000 (using Ctrl + End), yet I only have 10 rows of data and up to column J.

THis means the mailmerge produces 125 pages of labels when only 2 are needed.

I searched here and found code below, but this did not reset it.

How can I reset the used range with VBA please.
Whilst sheet A is giving me problems now, there might well be others, so I would like to reset each sheet before I process it.

TIA

Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim dummyRng As Range
Dim AnyMerged As Variant
'http://www.contextures.on.ca/xlfaqApp.html#Unused
'Helps to reset the usedrange by deleting rows and columns AFTER your true used range

    'Check for merged cells
    AnyMerged = ActiveSheet.UsedRange.MergeCells
    If AnyMerged = True Or IsNull(AnyMerged) Then
        MsgBox "There are merged cells on this sheet." & vbCrLf & _
               "The macro will not work with merged cells.", vbOKOnly + vbCritical, "Macro will be Stopped"
        Exit Sub
    End If

    With ActiveSheet
        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

End Sub

 

[welshgasman]

Just found this code, that despite it finding the last row and column both at 10, it seemed to work?

Would that be for the last line in the code, ActiveSheet.UsedRange

I did also select Cell A1 and save the file as mentioned in the thread that I found that code. Used Range won't reset

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

 

[welshgasman]

Well that does not work on two other sheets?
The row and column numbers always match.

Edit: The person who had entered the data had copied a formula down to row 36 in the last column.
Once I found that and removed excess formulae for empty rows that second code worked well.

 

[Alex Blakenburg]

I on a mobile at the moment. After running any clean the used range code you either need to have a line that includes the command UsedRange (which is where the AcitveSheet.UsedRange comes in) OR hit “Save” on your workbook.

On the sheets where it didn’t work, it is likely that resetting the RowHeight on the unused range will fix it.
See here
Can't reset the last used row when Activesheet.UsedRange is only entire columns
Note: Rory’s last comment and also backup your workbook before testing.

 

[welshgasman]

I on a mobile at the moment. After running any clean the used range code you either need to have a line that includes the command UsedRange (which is where the AcitveSheet.UsedRange comes in) OR hit “Save” on your workbook.

On the sheets where it didn’t work, it is likely that resetting the RowHeight on the unused range will fix it.
See here
Can't reset the last used row when Activesheet.UsedRange is only entire columns
Note: Rory’s last comment and also backup your workbook before testing.

Hi Alex,
Thanks for the reply. I reckon the second code sub is doing it, once I found some errant copied formulae.
Normally I would do the same, to save time, but with the mailmerge, it throws it right off.

 

[Alex Blakenburg]

Thanks for letting me know. Glad you got it sorted.