Sort table column left to right

[gaudrco]

Is there way to sort table columns left to right? That is, the column headers sort alphabetically along with all the data below the headers. But here's the catch; I need just a selection of columns sorted.

My table ("CompComparisonTable") has competitors as the column headers and with features as the rows. So the first column are features going down the rows and the second and third column have to do with my company so I want these to remain farthest left. Everything to the right of that are competitors. Right now, the spreadsheet is set up to have new competitors added to the end of the table, but after they are added, I would like the new competitor to sort alphabetically with the other competitors.

Any ideas?

 

[gaudrco]

Select G5 and go down the column using the down arrow.
Anything merged ?

I have unmerged all cells on that sheet (i think) and still no luck

Did you see my response at your other thread and try it to see what Excel is seeing as the range ?

Yes, I have checked this and the range is exactly what I am looking for.

Do you think the "List of Values" report list is causing a problem?

 

[NoSparks]

Yes, I have checked this and the range is exactly what I am looking for.

How did you check this ?
The Name Manager will indicate what you want, but
did you type the name into the name box on the formula bar and see the result that's selected ?

 

[gaudrco]

did you type the name into the name box on the formula bar and see the result that's selected ?

Yes I did this.

I mentioned before but I will point it out again. The sorting for this range ("DynamicDataList") does work if I assign the code to a button and place the button on that sheet ("Competitor Comparison Data").
It looks like this:

Private Sub CommandButton3_Click()
Range("DynamicDataList").Sort Key1:=Range("DynamicDataList"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlLeftToRight
End Sub

When I click this button, the sorting works

 

[NoSparks]

Only difference I can see would be the sheet that's active at the time.
Any use of Range without the dot will refer to the active sheet.
Using .Range inside the With will refer to whatever the with is and without the dot will refer to the active sheet.

 

[gaudrco]

Only difference I can see would be the sheet that's active at the time.
Any use of Range without the dot will refer to the active sheet.
Using .Range inside the With will refer to whatever the with is and without the dot will refer to the active sheet.

Private Sub CommandButton1_Click()


Set srtA = Sheets("Competitor Comparison")


With srtA
.ListObjects(1).Unlist
Range("DynamicCompList").Sort Key1:=Range("DynamicCompList"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlLeftToRight
ActiveSheet.ListObjects.Add(xlSrcRange, Range("DynamicCompTable"), , xlYes).Name = _
        "CompComparisonTable"
        
 Range("A8").Select


 Selection.AutoFilter
End With


Set srtB = Sheets("Competitor Comparison Data")
With srtB
[B].Range("DynamicDataList").Sort Key1:=Range("DynamicDataList"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlLeftToRight[/B]
End With
End Sub

Yeah I'm not sure whats going on here. I would bet it's something simple. The bold line is the line where this error comes up "Run-time error '1004': Method 'Range' of object '_Worksheet' failed"

 

[NoSparks]

What if you put a dot in front of the other use of Range in that line ?

 

[gaudrco]

I knew it was something simple! That suggestion makes logical sense to what you were saying here in post 34:

Any use of Range without the dot will refer to the active sheet.
Using .Range inside the With will refer to whatever the with is and without the dot will refer to the active sheet.

Thank you very much!