Having Problems with Concatenate Code

[Guzzlr]

Range("E7").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-1],RC[24],RC[25])"
    Range("E8").Select
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("E7").Select
    Selection.AutoFill Destination:=Range("E7:E179"), Type:=xlFillDefault

I used the Recorder to insert the application Concatenate. The problem here is the range down to E179 will change, every time with new data input. It may only go to E100, or E...whatever. I don't know how to have the application stop In Column E when it reaches the end of the current data set.
If I say Range("E7:E1000") it may not be far enough...or way too far.
How can I make it stop where it needs to stop at the end where there is no more data to concatenate?
Thanks for the help

 

[arunsjain]

Hi,

Very nice post on concatenate VBA

https://www.excelcampus.com/keyboard-shortcuts/concatenate-range-of-cells/

Hope it will help.

Cheers !!!

 

[Guzzlr]

I did not know about the key board short cut, I'll remember that one. I did know about the ampersand method. My problem is my concatenate is for many rows down to the bottom of the data set. If I choose F7&H7&J7 for concatenate cell of E7, the range for column E may be E7:E47. However, the very next time I run a data set on my template, it may be E7:E700. Column E will change distance to the bottom of the data set with each run. I still have not mastered how to get the formula to run to find the end where the data set ends and to stop. Or, drop to the bottom of Column E and work up 1 row at a time with Concatenate for each row on column E...
Thanks for the help.

 

[Guzzlr]

[COLOR=#000000][FONT=Courier]lRow = Cells(Rows.Count, 1).End(xlUp).Row[/FONT][/COLOR]

I do know a little about the .End(xlUp) code, to drop all the way to the bottom of a given column, and work up 1 row at at time. I just don't know how to incorporate the concatenate application. Lets say it drops all the way to the bottom of Column B, which will definitely be the longest column of data, and then works it's way up to B450, where there is a number. Presumably, E450 is where the Concatenate application will begin. So, is E450 where I write the concatenate code after the xlUp code, and the next line will be my next row line, to start the loop?
Thanks for the help

 

[Guzzlr]

  Range("F7").Select
            ActiveCell.FormulaR1C1 = "=RC[-1]&RC[24]&RC[25]"
            Range("F7").Select
            Selection.AutoFill Destination:=Range("F7:F750"), Type:=xlFillDefault

OK, so I have this now, and it will give me my concatenate for the given range. This is where I need help to find the last row of the work sheet, so excel will use this formula to fill Column F to wherever the last the longest column of the worksheet has data, which is usually column B.
Thanks for the help
excel 2013

 

[Guzzlr]

  Range("F7").Select
            Selection.Formula = "= E7& AD7 & AE7"
            Selection.AutoFill Destination:=Range("F7:F750"), Type:=xlFillDefault

Made it simpler now, just need the secret of finding bottom row and filling formula to last row with data in the work sheet
Thanks

 

[Guzzlr]

So I've made a test sheet with the formula:

Dim Col As Range
For Each Col In Range("C" & Rows.Count).End(xlUp)
Selection.Formula = "= B20 & D20 & E20"
Next Col

However
the formula keeps imputing into A1, and I don't know why.
With my test sheet, I'm trying to make the formula appear in Column C20 and work it's way up.
Need help please
thanks