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
Pete_UK <(E-Mail Removed)> wrote:
>Thanks for that. I have a few macros (which I only use occasionally)
>which seem to take an inordinate length of time to do something simple
>like delete a block of rows or columns, or just copying a 0 down a
>column, so I'll bear this in mind next time I use them and investigate
>further.
>
>Glad you found a solution to your problem.
>
>Pete
>
>On Jun 19, 3:38*pm, Eagle...@discussions.microsoft.com wrote:
>> Actually, I was doing that until I hit one single line
>>
>> * * Wks.Range("U:U,V:V,W:W,X:X,Y:Y").EntireColumn.Delete
>>
>> (I had previously formated those columns with a non-general numberformat. *I had no idea that that
>> would come back to haunt me - see below)
>>
>> This caused/causes major time issues. *I have made some headway though.
>>
>> I noticed that if I deleted columns 5+ columns to the right of any usedrange issues,
>> I had no problems. *BUT, if I attempted to delete any column subjected to formating
>> Time issues occured.
>>
>> My conclusion so far is formatting an entire Column or Columns with even a non-general
>> NumberFormat causes the entire column(s) to be-in-play. *Apparently, this becomes when the w/s has
>> quite a number of rows i.e. my case 47,000.
>>
>> My point, I have gone back and limited ranges to be formated i.e.:
>>
>> Not: * * Columns("T:T")..NumberFormat = "0_);(0)"
>>
>> Yes: * *Wks.myRange.Columns("T").NumberFormat = "0_);(0)"
>>
>>
>>
>> Pete_UK <pashu...@auditel.net> wrote:
>> >Well if you are developing and testing the macro as you go along, why
>> >don't you set up breakpoints at appropriate positions in the macro, so
>> >that you can run up to that point, switch to single-stepping, and
>> >abort if necessary?
>>
>> >Pete
>>
>> >On Jun 19, 1:45*pm, Eagle...@discussions.microsoft.com wrote:
>> >> Thanks for your information Pete. *That said, our posts may have crossed. *I had set the w/s to
>> >> manual, saved the file, made sure that the Autofilter was off, then selected an entire column, then
>> >> Excel grabbed 98% of the processor time and I was stuck again.
>>
>> >> For the life of me, I do not believe that I have seen this behavior before.
>>
>> >> Pete_UK <pashu...@auditel.net> wrote:
>> >> >Click on Tools | Options | Calculation tab, then ensure that Manual is
>> >> >checked. You can also uncheck the Recalculate before save option, so
>> >> >that you can save your changes to the macro more quickly.
>>
>> >> >Hope this helps.
>>
>> >> >Pete
>>
>> >> >On Jun 19, 1:22*pm, Eagle...@discussions.microsoft.com wrote:
>> >> >> Additional, the w/s in question has 47,000 rows.
>>
>> >> >> I did later than my original post, set the calculation to manual "Application.Calculation =
>> >> >> xlCalculationManual" in the Immediate window, (but not had saved the file - does that matter?). Then
>> >> >> I (just) selected on a single column and the w/s started calculating at the moment of my selection.
>>
>> >> >> Why is this happening?
>>
>> >> >> TIA EagleOne
>>
>> >> >> Eagle...@discussions.microsoft.com wrote:
>> >> >> >2003
>>
>> >> >> >Forgot to turn Calculate to manual - then ran a macro.
>>
>> >> >> >Attempted to "Esc" and "Ctrl-Break" often but to no avail.
>>
>> >> >> >I am testing VBA and no real need to obtain or hold the data (copy of orig file)
>>
>> >> >> >Is there a way to abort the calculation in progress, to be able to save any changes in VBA Code
>> >> >> >being tested?
>>
>> >> >> >TIA Eagleone- Hide quoted text -
>>
>> >> >> - Show quoted text -- Hide quoted text -
>>
>> >> - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
Similar Threads
