Coding a Variable Range in one Column in Excel
Posted on 2003-11-05
I'm trying to automate a function in Excel where a specific column receives the end result of concatenating LName,FName MI into an inserted column. I receive an Excel file daily with the information where this function needs to be done.
My problem is that every day I don't receive the same amount of rows of names that need to be concatenated and I can't seem to figure out how to NOT hard code the Range and get it to work.
In the code below you'll notice the Range is from rows B2 to B25. The next day, I may only get 10 rows or the following day 50 rows, etc, etc.
If I expand the Range out beyond the amount of rows I know I'll get (lets say B100), I end up with a column of "," (commas) after the concatenated names and then the other function I have beyond that (save as a CSV file), I end up with all the extra commas in the end that I don't want. I couldn't seem to reverse engineer anything to get that working.
I was hoping not to have to go in and change the hard code every time I get a new file as I wanted to just have the user click the button and it will adjust to the amount of rows for that file for that day.
Is there code that will allow this to happen?
I'm quite new at VB so if you can offer assistance, I'm in the "literal...need it spelled out" mode my learning curve is still high. Any assistance would be greatly appreciated, as my multiple attempts have not worked.
This is the code I have thus far....
'Changes name format
ActiveCell.FormulaR1C1 = "=RC&"",""&RC&"" ""&RC"
Selection.AutoFill Destination:=Range("B2:B25"), _Type:=xlFillDefault