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?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.