My code is not dynamic; it has a pre-defined range. Possible to change code?

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!

Hi Humdaingaling,

Thanks for taking a look at my problem. My last column actually is much further than BF. However, from column BG and beyond, I sort those columns separately.

1. First I need to sort Area 1 (columns B:BF) A to Z, but as time goes on, BF may extend more columns to the right.
2. Then, I need to sort Area 2 (BE:LJ) A to Z after that. Right now the end of the range is LJ, but the columns keep growing. Also, if area 1 was to grow more columns to the right, then I need Area 2's range to begin where Area 1 ends.

Area 1, as I mentioned in the OP, needs to end at the column before the code finds "# Program Placeholder", and Area 2 needs to begin at "# Program Placeholder" and then end in the last column with data.

Not sure if this made it any more clear?

Originally Posted by humdingaling

not quite sure if i understand you but i mocked up some data

see if it help you identify what you need?

its basically to find the range...once you have the range you should be able to do the sorting bit

Your data mockup more or less describes what is going on exactly.

It is finding for the first part, B1:BE50 correctly. This is the expected behavior.

The 2nd part is finding B50:LJ58. This is not what I expected. The second part should find BF1:LJ50

I got the expected ranges by making some slight adjustments, here:

  j = Cells(Rows.Count, "B").End(xlUp).Row
    k = Application.WorksheetFunction.Match("# Program Placeholder", Range("B1:lj1"), 0)
    
    
    MsgBox Range(Cells(1, 2), Cells(j, k)).Address
    MsgBox Range(Cells(j, k + 1), Cells(1, Range("b1").End(xlToRight).Column)).Address

I am trying to figure out how to incorporate this into my existing code now...

I managed to get it working by taking some of your code and adding in a few new letters to reference. I am sure this isn't a great way to do it, but it is working at least. I know that J isn't used anywhere anymore, but I kept it anyways because maybe I will use a similar code for something in the future.

Sub ProgramSetup()
'This Macro will run all of the macros needed for maintenance of the Program Setup worksheet.  All macros are contained below in this module
'This macro also makes forms pop up to notify the user that the macro is in progress and again once it finishes.
Range("A1").Select
Application.ScreenUpdating = True
UserForm_ProcessingSort.Show False
Application.ScreenUpdating = False
DoEvents
Application.Wait Now + TimeValue("00:00:01")
Call AutoFit
Call Sort_Markets
Call Sort_Programs
Call Sort_MarketList
Call Activate_TopLeft
Application.ScreenUpdating = True
UserForm_ProcessingSort.Hide
FRM_SortComplete.Show False
End Sub

Sub Sort_Programs()
' This macro will determines the range of columns that have 'Program' data in them and it will sort the
' program data columns horizontally in A-Z order.

    j = Cells(Rows.Count, "B").End(xlUp).Row
    k = Application.WorksheetFunction.Match("# Program Placeholder", Range("B1:AZZ1"), 0)
    l = Range(Cells(1, 2), Cells(300, k)).Address
    m = Range(Cells(300, k + 1), Cells(1, Range("b1").End(xlToRight).Column)).Address
    n = Cells(1, k + 1).Address
    
With Range(m)
    .Rows.Sort Key1:=.Rows.Range(n), Order1:=xlAscending, Orientation:=xlLeftToRight
    End With
End Sub

Sub Sort_Markets()
' This macro will determines the range of columns that have 'Market' data in them and it will sort the
' market data columns horizontally in A-Z order.

    j = Cells(Rows.Count, "B").End(xlUp).Row
    k = Application.WorksheetFunction.Match("# Program Placeholder", Range("B1:AZZ1"), 0)
    l = Range(Cells(1, 2), Cells(300, k)).Address
    m = Range(Cells(300, k + 1), Cells(1, Range("b1").End(xlToRight).Column)).Address

With Range(l)
    .Rows.Sort Key1:=.Rows.Range("B1"), Order1:=xlAscending, Orientation:=xlLeftToRight
    End With
End Sub

Humdingaling,

Thanks a ton for the help, this actually helped me to learn a bit more about how I can construct VBA for the future.