Automatically Sort Tables with Headers (VBA)

dmacch24

New Member
Joined
Apr 10, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. 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:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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
 
Upvote 0
Can you post a sample of your data using the XL2BB add-in, see my signature for the link
 
Upvote 0
For some reason, after I add the add in it's not letting me post
 
Upvote 0
Did you unzip the file & move the add-in to your add-ins folder?
If so did you also unblock it?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top