VBA: How To Sort Rows with formulas?

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
I've got a pretty simple table of data, that I'm trying to sort, and am having trouble figuring out how to do it. In column A, I've got unique values from a full table of data on another sheet, Columns B, C, and D all have formulas that are generating values based on what's in column A of that row. I'm trying to write a macro that when you select the header of a column, it will sort the 4 columns in ascending order based on the selected column. My problem is that it's doing that in a way that it is changing the order of the formulas, rather than just sorting column A, and leaving the formulas alone. Can anyone explain to me how I can sort Column A based on the values in the other columns, without sorting my formulas? Here's my code

VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim wks                 As Worksheet
    Dim tar_RNG             As Range
    Dim d_RNG               As Range
    
    Application.ScreenUpdating = False
    
    Set wks = ThisWorkbook.Sheets(Target.Parent.Name)
    Set tar_RNG = wks.Range("A1:D1")
    Set d_RNG = wks.Range(wks.Cells(1, 1), wks.Cells(wks.Cells(Rows.Count, 1).End(xlUp).Row, 4))
    
    If Not Application.Intersect(Target, tar_RNG) Is Nothing Then
        d_RNG.Sort Key1:=Target, Order1:=xlAscending, Header:=xlYes
    End If
    
    Set d_RNG = Nothing
    Set tar_RNG = Nothing
    Set wks = Nothing
    Application.ScreenUpdating = True
End Sub

Sheet:
Header: LocationHeader: # of ProceduresHeader: AVG Days to HealHeader: # of Cases w/ Complications
Left: Arch=countif('Tenex DFU'!$f:$:4,Analytics!A2=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A2,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A2,'Tenex DFU'!O:O,"Yes")
Left: Cuneiform=countif('Tenex DFU'!$f:$:4,Analytics!A3=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A3,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A3,'Tenex DFU'!O:O,"Yes")
Left: Distal Phalanx Great Toe=countif('Tenex DFU'!$f:$:4,Analytics!A4=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A4,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A4,'Tenex DFU'!O:O,"Yes")
Left: Fifth IPJ=countif('Tenex DFU'!$f:$:4,Analytics!A5=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A5,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A5,'Tenex DFU'!O:O,"Yes")

Output:
Header: LocationHeader: # of ProceduresHeader: AVG Days to HealHeader: # of Cases w/ Complications
Left: Arch17.00
Left: Cuneiform118.00
Left: Distal Phalanx Great Toe234.51
Left: Fifth IPJ10.00

After Sorting using Col C (as an example):
Header: LocationHeader: # of ProceduresHeader: AVG Days to HealHeader: # of Cases w/ Complications
Left: Fifth IPJ=countif('Tenex DFU'!$f:$:4,Analytics!A5=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A5,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A5,'Tenex DFU'!O:O,"Yes")
Left: Arch=countif('Tenex DFU'!$f:$:4,Analytics!A2=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A2,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A2,'Tenex DFU'!O:O,"Yes")
Left: Cuneiform=countif('Tenex DFU'!$f:$:4,Analytics!A3=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A3,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A3,'Tenex DFU'!O:O,"Yes")
Left: Distal Phalanx Great Toe=countif('Tenex DFU'!$f:$:4,Analytics!A4=IFERROR(AVERAGEIFS('Tenex DFU'!$J:$J,'Tenex DFU'!$F:$F,Analytics!A4,'Tenex DFU'!$J:$J,"<>NA"),"NA")=COUNTIFS('Tenex DFU'!F:F,Analytics!A4,'Tenex DFU'!O:O,"Yes")


My goal is to just sort column A based on the values in the selected column, but leave the formulas alone in the other 3 columns. Any suggestions or ideas?

Thanks so much.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
Try changing the formulae so that they do not use the active sheets name, like
=COUNTIFS('Tenex DFU'!F:F,A2,'Tenex DFU'!O:O,"Yes")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
Why is it that referencing the sheet name causes it to act differently?
Absolutely no idea, I just learnt that it's better not to refer to the sheet that contains the formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,833
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

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