PDA

View Full Version : [SOLVED] Remove extra commas from end of content



Denblanc
03-15-2016, 05:16 PM
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
03-15-2016, 06:05 PM
Use xlUp rather than xlDown.

Denblanc
03-15-2016, 06:09 PM
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
03-15-2016, 10:34 PM
.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
03-16-2016, 02:14 AM
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!