Column Remains selected after Macro is selected

Livin404

Active Member
Joined
Jan 7, 2019
Messages
460
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings,
I have the following vba and it works find; however after the macro is activated it leaves the first column A selected. Is there something I can do, to remove the appearance of it being selected.
The VBA is:
VBA Code:
Sub AoutboundSort()
'
' AoutboundSort
''
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Add2 Key:=Range _
        ("A1:A1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Air Outbound").Sort
        .SetRange Range("A1:A1048576")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.RefreshAll
End Sub


Thank you very much indeed.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Remove the columns "A:A" .select
VBA Code:
  Sub AoutboundSort()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Add2 Key:=Range _
        ("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Air Outbound").Sort
        .SetRange Range("A1:A" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.RefreshAll
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad to help. .😎
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
460
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Remove the columns "A:A" .select
VBA Code:
  Sub AoutboundSort()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Add2 Key:=Range _
        ("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Air Outbound").Sort
        .SetRange Range("A1:A" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.RefreshAll
Hello, I initially thought it worked. It did get rid of the "selection" appearance. However, the formula did not sequence the dates Column A by Oldest to Newest. Mine did work, but I had the issues with the first column appearing selected.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
The changes should have no effect on the sorting !
BUT as an alternative try
Rich (BB code):
Sub AoutboundSort()
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Air Outbound").Sort.SortFields.Add2 Key:=Range _
        ("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Air Outbound").Sort
        .SetRange Range("A1:A" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.RefreshAll
range("A1").select
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
460
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Hello, I'm afraid it's not sequencing it. My original formal is working, it is just leaving Column A selected. Maybe I can type in Range ("A1"). Select at the end. I hope that will work. You've been very patient, thank you.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,921
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I put that into the code on the latest post #6
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
460
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Thank you, I’m so annoyed with myself, I’m getting the first column to resequence by oldest to Newest, but the other 8 or 9 Columns are not. When I recorded the macro I did ask it to extend the columns. This is used for a database which I’m putting it into a pivot table, thus my refresh and ensuring all entries are in order which is why I have the sort option. Thank you
 

Forum statistics

Threads
1,141,771
Messages
5,708,441
Members
421,570
Latest member
BaileyJ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top