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?

 

[NoSparks]

Its populated manually.

In that case then, isn't it conceivable that the CompComparisonTable not even exist ?
Being totally derived from the "Competitor Comparison Data" sheet couldn't that sheet be filtered and copied to what is now the CompComparisonTable eliminating the need for any formulas and eliminating the maintaining of two things on different sheets in order for them to jibe with each other ?

 

[gaudrco]

The Competitor Comparison sheet is used for reporting. The sheet includes checkboxes that allow users to hide and show certain columns and to be able to chose which report they want to see. As far as displaying data, this sheet is more visually appealing; its a reporting sheet. The Competitor Comparison Data is strictly for capturing the raw data.

 

[NoSparks]

Exactly.
In my mind you should be looking into making your raw data a proper Excel Table so you only have one thing to maintain,
and can use filtering to extract anything you want for coping to your 'visually appealing report sheets'.

 

[gaudrco]

Perhaps I could have designed my spreadsheet differently. I didn't realize how much you could do with VBA (I wouldn't have been able to do it without the help I have received on this forum so thanks all). That being said, I think I can make this work if this last piece is solved. I'm using this formula to refer to ("DynamicListData")

='Competitor Comparison Data'!$H$5:INDEX('Competitor Comparison Data'!$5:$1048576,COUNTA('Competitor Comparison Data'!$H:$H),COUNTA('Competitor Comparison Data'!$5:$5)+5)

This makes my range dynamic but it is not reaching the bottom of my spreadsheet. I need it to be dynamic and reach at least 3000 rows down. It currently only goes 66 rows down.

 

[NoSparks]

CountA counts cells that aren't empty, so guessing the 3000 rows you're wanting to use are mostly blank records.
Dynamic ?? Didn't you kill that with the merged cells in column D ?

 

[gaudrco]

Yes the 3000 rows are mostly emtpy. And I suppose I could unmerge the cells in column D. The reason they are merged is because is visually helps distinguish the different report. I could just color the cells in the group white so it looks like its merge; if that allows me to go down 3000 rows for the dynamic range

 

[gaudrco]

Thank you No Sparks for solving the dynamic range piece.

The code I am using to achieve the sort looks like this. I marked with asterisks the piece that is not working. When I run the code, it has a "Range of object error"

Private Sub CommandButton1_Click()
'First part
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
'**************
'Second part
Set srtB = Sheets("Competitor Comparison Data")
With srtB
Range("DynamicDataList").Sort Key1:=Range("DynamicDataList"), Order1:=xlAscending, Header:=xlYes, Orientation:=xlLeftToRight
End With
'**************
End Sub

I'm not sure if I'm referencing the object correctly. The button that runs this code is on Sheet ("Competitor Comparison").
The first part of the code sorts the columns on Sheet ("Competitor Comparison").
^This works perfectly

The second part of the code sorts the columns on Sheet ("Competitor Comparison Data").
^This code is functional because it will run if I assign the code to a button that is on Sheet ("Competitor Comparison Data") but it will not run when it is combined with the code that is on Sheet ("Competitor Comparison"). I need both parts to run on a single button click.

What am I doing wrong?

 

[NoSparks]

You talk 3000 rows but based on the file I have, the 'groups' of 50 rows on the Competitor Comparison Data sheet goes to row 2452.
40 rows have data and 2407 rows are blank, 47 of those blank rows have merged cells.
Those merged cells are killing you.


I think you should be using .Range("DynamicDataList")

 

[gaudrco]

Those merged cells are killing you.

So the range ("DynamicDataList") does not include the column with the merged cells so I don't think it is affecting anything in this code. However, I did try unmerging the cells and the code still did not work with the same error.

I included the period that you added below, and it did not work:
.Range("DynamicDataList")

 

[NoSparks]

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

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