Remove extra commas from end of content

[Denblanc]

Hello everyone,
I hope this is something simple to fix. My VBA code inserts a formula into Column "D" to combine Columns "C & D" that is separated by a comma. The problem is when the content ends (number of rows varies) commas are inserted in the blank rows until the great beyond.

      Range("D4:D" & .Range("D" & .Rows.Count).End(xlDown).row).Formula = "=(C4&"", ""&B4)"

Any suggestions on whether to delete the rows or what to insert to stop at end of content?
Thanks for your help!

[mikerickson]

Use xlUp rather than xlDown.

[Denblanc]

My code designates what row to start at then work down. How do I designate an end row when I don't know what it is?

[mikerickson]

.End(xlDown) is down from the indicated cell.
In your construction, the designated cell is Range("D" & .Rows.Count) i.e D1048576.

You don't want to go down from that, you want to go up until the last filled cell.
That would be Range("D" & .Rows.Count).End(xlUp)

I would write your code

With Range("D:D")
    Range(.Cells(4, 1), .Cells(.Rows.Count,1).End(xlUp)).Formula = "=SUBSTITUTE(TRIM(C4 & "" "" & B4), "" "" , "", "")"
End With

(If you expect no spaces in the cells being concatenated)

[Denblanc]

I tried your code and it only changes rows 3 & 4 and overwrites my column header - using xlUp. I used your code but changed to xlDown it works perfectly, combines both columns and eradicates all the extra commas that was in the rows after the content ended.

Thanks a lot for all your help!