Event macro: insert row with formulae based on current row, clear selected contents

NoSparks · #1 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.

emsa · #2 04-17-2020, 09:03 AM

Good afternoon NoSparks,

Your latest line of code works like a dream thank you so much. Now that there are two lines that will work which one do you recommend I should use in my macro?

Is the RemoveExcessiveUsedRange macro something I can run on occasion should I come across any problems?

Once again, thank you so much for your time.

Kind regards,

emsa

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Macro - Copying and Pasting selected slides (based on selected keywords/criteria) Hi	erickhawe	PowerPoint	0	08-16-2019 09:00 PM
Need macro for automatic point value insertion based on the number of answer choices selected	ganesang	Word VBA	29	08-12-2018 09:38 PM
Need macro for automatic point value insertion based on the number of answer choices selected	ganesang	Word	1	07-31-2018 06:07 AM
a macro that can copy data from copy.xls to our current excel macro.xls based on criteria:	udhaya	Excel Programming	1	11-12-2015 10:12 AM
Need macro to fill data from different sheets based on selected item from drop down	skorasika	Excel Programming	1	03-13-2015 11:25 AM