Pete, I confirmed my thoughts. My UsedRange was A1:Z65536.
That is why the "time" issue
I used the following VBA which I developed as a direct result of quite a few
Excel MVP's and others.
Good Luck
*********************************************************************************
Sub UsedRangeReset()
'
' Calculates ACTUAL Used Range (with real data not just formatting only)
' and resets used range to Rows & Columns with real data
'
With Application 'Turns On the Auto-calculate and Screen-updating features of XL
.Calculation = xlManual
.MaxChange = 0.001
.ScreenUpdating = False
End With
Dim CellsBefore As Double, CellsAfter As Double
Dim myRowsToProcess As Double, myColumnsToProcess As Double
Dim myOrigSheetProtectStatus As Boolean
Dim MyPreviousWorkBook As Workbook
Dim MyPreviousWorksheet As Worksheet
Set MyPreviousWorkBook = ActiveWorkbook
Set MyPreviousWorksheet = ActiveSheet
If MyPreviousWorkBook.Saved = False Then
MyPreviousWorkBook.Save
End If
ActiveWindow.FreezePanes = False ' Turns off Freeze Panes
ActiveSheet.AutoFilterMode = False ' Turns off AutoFilter
With ActiveWindow ' Removes Splits
.SplitColumn = 0
.SplitRow = 0
End With ' Removes Splits
CellsBefore = ActiveSheet.UsedRange.Cells.Count
If CellsBefore = 0 Or CellsBefore = 1 Then
MsgBox "[ " & ActiveSheet.Name & " ]" & " has no Data Cells"
With Application 'Turns On the Auto-calculate and Screen-updating features of XL
.Calculation = xlAutomatic
.MaxChange = 0.001
.ScreenUpdating = True
End With
Exit Sub
End If
On Error Resume Next
Cells.SpecialCells(xlConstants, 23).Select
If Not Err.Number > 0 Then
With ActiveSheet
MaxRows = .Rows.Count
MaxColumns = .Columns.Count
End With
myRowsToProcess = Cells.Find(What:="*", After:=ActiveSheet.Cells(1, 1), LookIn:=xlFormulas,
_
LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
myColumnsToProcess = Cells.Find(What:="*", After:=ActiveSheet.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
myRowsToProcess = IIf(myRowsToProcess > MaxRows, MaxRows, myRowsToProcess)
myColumnsToProcess = IIf(myColumnsToProcess > MaxColumns, MaxColumns, myColumnsToProcess)
Else
MsgBox ActiveSheet.Name + " is Empty!"
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
.ScreenUpdating = True
End With
Range("A1").Select
On Error GoTo If_Error
End If
Range(Cells(1, myColumnsToProcess + 1), Cells(65536, 256)).EntireColumn.Delete
Range(Cells(myRowsToProcess + 1, 1), Cells(65536, 256)).EntireRow.Delete
ActiveSheet.UsedRange
CellsAfter = ActiveSheet.UsedRange.Cells.Count
With Application 'Turns On the Auto-calculate and Screen-updating features of XL
.Calculation = xlAutomatic
.MaxChange = 0.001
.ScreenUpdating = True
End With
Range("A1").Select
MsgBox "[ " & ActiveSheet.Name & " ]" & " Cells cleared from memory " _
& Format((CellsBefore - CellsAfter), "#,##0") & Chr(10) & Chr(10) & _
"Process Completed! Press OK to Continue"
If MyPreviousWorkBook.Saved = False Then
MyPreviousWorkBook.Save
End If
ActiveSheet.EnableSelection = xlNoRestrictions
Exit Sub ' Must Exit Sub before Error Handling
If_Error:
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
.ScreenUpdating = True
End With
Range("A1").Select
MsgBox "ALERT! " & Err.Number & " " + Err.Description & " [Worksheet " & ActiveSheet.Name _
& " Row: " & RowCounter & "]"
End Sub