Something is slowing down my code...

[zombiemaster]

Hoping a second set of eyes can help speed up this VBA.

I created a process where I copy information from one spreadsheet (used by our Contact Center to track call data) and paste it into a "transition sheet" - then I click my button to run my code that moves stuff around, format dates/times into just dates, etc. so I can get it into a format to copy/paste into a third sheet used by my department for processing the calls.

When I just run the macro normally, it takes 3 minutes to finish. It's not that huge of a macro, either, and the dataset is small (less than 25 rows of data each time) so I don't know what is slowing it down. I am hoping someone here can take a look and maybe see something that I'm not. I'm far from an expert, and I know I have some repetition here that could probably be avoided, but that shouldn't slow it down this much. Any help is appreciated!

When I run it a few steps at a time in VBA editor using break/stops, it only takes about 20 seconds to get through it. Something here doesn't seem right to me...lol

Thanks for looking!
-=ZM=-

Sub Transition()
'
' Fixing the time stamp issues in column B

    Range("A65536").Select
    Selection.End(xlUp).Select
    endRow$ = ActiveCell.Row
    Range("B7:B" + endRow$).Select
   
    Dim A As Range
        Set A = Selection
    For Each cell In A
        cell.Value = WorksheetFunction.Trim(cell)
    Next
   
    Sheets("To Support Tracking").Visible = True
    Sheets("To Support Tracking").Select
    Application.ScreenUpdating = False
    Range("A65536").Select
    Selection.End(xlUp).Select
    endRow$ = ActiveCell.Row
    Range("A2:F" + endRow$).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H2:S" + endRow$).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select

' Sort - "Z to A" to bring actual items to the top of the list so blank rows are ignored

    Range("A2:V" + endRow$).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("To Support Tracking").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("To Support Tracking").Sort.SortFields.Add2 Key:= _
        Range("I2:I" + endRow$), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("To Support Tracking").Sort
        .SetRange Range("A1:V" + endRow$)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
    Application.ScreenUpdating = True

' Gets rid of Time stamp in column A

    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 3), Array(2, 9), Array(3, 9)), TrailingMinusNumbers:=True
    Selection.NumberFormat = "m/d/yyyy"
    Application.Goto Reference:="R2C1"

' Eliminates periods and commas from the Name columns

    Application.ScreenUpdating = False
    Range("A65536").Select
    Selection.End(xlUp).Select
    endRow$ = ActiveCell.Row
    Range("D2:E" + endRow$).Select
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

' Hides the From Contact Center tab:

    Sheets("From Contact Center").Visible = False

    Range("A2").Select


End Sub

 

[Michael M]

A couple of things
1. you need to stop Selecting cells
2. Put application.screenupdating =false at the start of the code and application.screenupdating =true at the end of the code

This for example

 Range("A65536").Select
    Selection.End(xlUp).Select
    endRow$ = ActiveCell.Row
    Range("B7:B" + endRow$).Select

could be changed to

dim lr as long
lr = cells(rows.count,"A").end(xlup).row
Range("B7:B" & lr).Select

 

[zombiemaster]

A couple of things
1. you need to stop Selecting cells
2. Put application.screenupdating =false at the start of the code and application.screenupdating =true at the end of the code

This for example

 Range("A65536").Select
    Selection.End(xlUp).Select
    endRow$ = ActiveCell.Row
    Range("B7:B" + endRow$).Select

could be changed to

dim lr as long
lr = cells(rows.count,"A").end(xlup).row
Range("B7:B" & lr).Select

Thanks, Michael - I updated my 'selecting' to the 'dim lr' as you suggested, - I don't know what that means exactly (not an expert as I said) but it did seem to work! I also moved my screenupdating=false to the very top of the list and added the true to the bottom (I thought I already had that there, my bad).

I had to remove other pieces of code to avoid errors and will need to verify I'm not losing any data somewhere, but so far this is very promising...

One question though before I sign off for the night - I'm getting a "there's already data here, do you want to replace it" message - is there a way to avoid that and just have it overwrite the data?

Thanks so much for your quick response and the help!
-=ZM=-

 

[Michael M]

You can change that in the settings

1. Go to File > Options > Advanced > Editing options > Enable fill handle and cell drag-and-drop.
2. Un-check "Alert before overwriting cells"

 

[zombiemaster]

You can change that in the settings

1. Go to File > Options > Advanced > Editing options > Enable fill handle and cell drag-and-drop.
2. Un-check "Alert before overwriting cells"

Thanks again for the quick response - turning it off for all of Excel isn't what I had in mind so I did a little digging and found Application.DisplayAlerts = False which will do what I need for this process. Thank you for your help!

-=ZM=-

 

[Michael M]

Glad you got it sorted...