Microsoft Office Forums - View Single Post - Event macro: insert row with formulae based on current row, clear selected contents

NoSparks · 04-17-2020, 03:51 AM

Formatting entire rows (that's 16,384 columns wide) or entire columns (that's 1,048,576 rows high) can make Excel think the UsedRange is greater than it really is.
I ran a little macro on your "Planning" sheet to reset the UsedRange. The originally suggested line of code then worked as offsetting of the UsedRange no longer tried to exceed Excel's last column.

Code:

Sub RemoveExcessiveUsedRange()
' reduce usedrange to what's actually used
    Dim Lastrow As Long, LastCol As Long

    Application.ScreenUpdating = False
    On Error Resume Next
    With Sheets("Planning") 'change sheet name as required
        Lastrow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
        .Range(.Cells(Lastrow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
    End With
    On Error GoTo 0
    Application.ScreenUpdating = True
End Sub

prior to running the macro:
?activesheet.usedrange.rows.count
683
?activesheet.usedrange.columns.count
16384
after running the macro:
?activesheet.usedrange.rows.count
41
?activesheet.usedrange.columns.count
18

Hope this helps.