sorting columns based on row criteria, with variable number of columns

I'm trying to put some macro buttons on a template that teachers use to analyze testing data. The template has a column for each question. The questions always begin on column C, but may range to any column depending on the number of questions on the test. The buttons would sort the questions by different rows (question #, % correct, type of question, etc.)

The following recorded macro does what I want, except, the range will not always be what it recorded here. It may be from C:BD or from C:Z or whatever else. How can I adapt the code to adjust for whatever number of columns there might be?

I would also like to have a similar command for another section of columns in the template (from BL:DE in the attached sample), but in that case both the beginning and ending columns would be variable. That section would always begin two columns to the right of the "Total Tested" columns.

A sample file is attached and linked to below.

Sub Macro11()

    Columns("C:C").Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Add Key:=Range( _
        "C8:BD8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TestResults").Sort
        .SetRange Range("C1:BD382")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Attachment 341532

https://drive.google.com/file/d/0B0N...it?usp=sharing

Maybe:

Sub bardobhb()
Dim x As Long
x = ActiveSheet.UsedRange.Columns.count + 1
    Columns("C:C").Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Add Key:=Range(Cells(8, "C"), Cells(8, x)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TestResults").Sort
        .SetRange Range(Cells(1, "C"), Cells(382, x))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I very much appreciate the effort, but it doesn't seem to do it. On the attached file, the code you provided seems to include in the sorting range the black columns to the right of the questions. It should stop at the last question (after which there currently exists a thin blank column), as if one was hitting CTRL+Shift+Right Arrow to select from C to the end. Any possibilities there? Thank you again.

I can't open the attachment. I get a message saying it is invalid?

Hopefully this attachment works better. There's also a google drive link in the original post.

test view sample1.xlsx

If your format offsets 8 each time between questions then maybe:

Sub bardobhb()
Dim x As Long
Dim y As Long
x = Range("C1").End(xlToRight).Column
    Columns("C:C").Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Add Key:=Range(Cells(8, "C"), Cells(8, x)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TestResults").Sort
        .SetRange Range(Cells(1, "C"), Cells(382, x))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    
 y = Cells(1, x + 8).End(xlToRight).Column
        
    Columns(x + 8).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TestResults").Sort.SortFields.Add Key:=Range(Cells(8, x + 8), Cells(8, y)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("TestResults").Sort
        .SetRange Range(Cells(1, x + 8), Cells(382, y))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

I got the basic principle to work with the following code:

Sub readinessonlyTEKS()
Dim x As Long
Dim y As Long

x = Range("C1").End(xlToRight).Column
y = Cells(1, x + 8).End(xlToRight).Column

    Application.ScreenUpdating = False
        Columns(x + 8).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.EntireColumn.Hidden = False
        Dim c As Range
        For Each c In Range(Cells(10, x + 8), Cells(10, y))
            c.EntireColumn.Hidden = (c.Value <> "m5")
        Next c
    Application.ScreenUpdating = True
End Sub

But instead of "m5" in the third to last line, I would want it to recognize [R] with wild cards on either side of it, but *[R]* doesn't work.

Any ideas? I know I could get it to work by creating a helper row that just had the [R], but it would be nice to avoid that extra step.

Sorry, was off this project for a few days, and just saw your post. It works perfectly, selecting either range perfectly no matter the number of columns, and I've been able to adapt it for several similar commands. Thanks so much for solving this question.

Would it be adaptable to hide columns according to a certain criteria? For example, in the range that is defined as x in your code above, let's say I don't want to sort it, but rather hide all columns where the value of the cell in Row 10 doesn't contain [R].

I had some similar code from another setting that did this. It was:

Sub DA()
    Application.ScreenUpdating = False
    Range("B:EZ").Select
    Selection.EntireColumn.Hidden = False
    Range("B1").Select
    Dim c As Range
    For Each c In Range("E5:EZ5")
        c.EntireColumn.Hidden = (c.Value <> "DA")
    Next c
    Application.ScreenUpdating = True
End Sub

Here is my attempt to adapt your code and the other to accomplish what I mentioned above, but it doesn't quite go:

Sub readinessonlyTEKS()
Dim x As Long
Dim y As Long

x = Range("C1").End(xlToRight).Column
y = Cells(1, x + 8).End(xlToRight).Column

    Columns(x + 8).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireColumn.Hidden = False
    Dim c As Range
    For Each c In Cells(10, x + 8).End(xlToRight)
        c.EntireColumn.Hidden = (c.Value <> "=*[R]*")
    Next c

End Sub

Any thoughts, or should I start a new thread?