Automatically Sort Tables with Headers (VBA)

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
2019
Platform
Windows
Hi


I'm trying to automatically sort the 2 tables on a spreadsheet using the macro below, however when the macro runs, it sorts the headers to the bottom even though I wrote xlYes for header. I think this may have something to do with the fact that there is data above the headers because it works when there is nothing on top of the headers.

The tables are located from C2:D5 and C12:D15 with numbers in cells C1,D1,C11, and D11 above the headers counting the number of cells.

Any suggestions on how to fix? Thanks! I've attached a screenshot of the worksheet as well.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("A1:D5")) Is Nothing Then
Range("c2").Sort Key1:=Range("c3"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
If Not Intersect(Target, Range("A1:D14")) Is Nothing Then
Range("c12").Sort Key1:=Range("c13"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
Cross posted Automatically Sort Tables with Headers

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
2019
Platform
Windows
Cross posted Automatically Sort Tables with Headers

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
Will do, sorry I'm new to the site
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
Can you post a sample of your data using the XL2BB add-in, see my signature for the link
 

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
2019
Platform
Windows
For some reason, after I add the add in it's not letting me post
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
Did you unzip the file & move the add-in to your add-ins folder?
If so did you also unblock it?
 

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
2019
Platform
Windows
Here is the output when I hit "capture range" for my data



vba test.xlsm
ABCD
156
2anumbertext
32hi
41hey
53hello
6
7
8
9
10
1178
12numbertext
131a
142b
153c
Sheet1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,849
Office Version
365
Platform
Windows
Ok, thanks for that. How about making this change
Rich (BB code):
Range("C2:D5").Sort Key1:=Range("C2"), _
And the same for the other range
 

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
2019
Platform
Windows
Ok, thanks for that. How about making this change
Rich (BB code):
Range("C2:D5").Sort Key1:=Range("C2"), _
And the same for the other range
It worked...thanks so much again!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,369
Messages
5,468,219
Members
406,574
Latest member
HeinrichPaul

This Week's Hot Topics

Top