Hi all,

I was inspired by some help I received on here earlier today to fix some code that was concerning me because it relied on a hard coded column number. I have a similar problem with this code where I have hard coded in the range of the data, but a more robust solution is that the code would search for the correct end of the range.

Basically, I need to sort some columns themselves in Alphabetical ascending order. The code below does that. What I didn't know how to do is to tell it where the range needs to end, so I hard coded it. A better solution would be for the macro to know where to end the range by searching for a string and then offsetting the range 1 to the left of where it found that string.

In Row B, the first column header which should NOT be included in range has the name "# Program Placeholder". I need the code to find that header name, and then define the range as being offset 1 column to the left of it. Currently, "# Program Placeholder" is in column BG (BG1) and so I have hard coded the range to be B1:BF1

Is this possible?

Sub Sort_Markets()
' If the LAST column that contains "ZoneYear" data changes, the reference to it below will need to change as well
With Range("B1:BF100") 'Change the column in this range according to the actual LAST column of "ZoneYear" data in the worksheet
    .Rows.Sort Key1:=.Rows.Range("B1"), Order1:=xlAscending, _
    Orientation:=xlLeftToRight
    End With
End Sub
If this is possible, I have another one that is almost the same need. The hard coded range BEGINS where "# Program Placeholder" is located and then I have it ending in some far far away column. An even better solution for this would be for the code to find where "# Program Placeholder" is and then set the beginning range for that cell, then find the last column where row 1 has data in it and set the end range for that cell.

Sub Sort_Programs()
' If the first column that contains "Program" data changes, the 2 references to it below will need to change as well
With Range("BG1:ABK40") 'Change the column in this range and the one in the next line according to the actual first column of program data in the worksheet
    .Rows.Sort Key1:=.Rows.Range("BG1"), Order1:=xlAscending, _
    Orientation:=xlLeftToRight
    End With
End Sub
Thanks for your time and expertise!