Rows C-F have the address. I created a new column and need the address in one column,row J

Rows C-F have the address of the users. I created a new column, and I need the address in just one column, (Row J)

In this format: Street address, city, state ZIP. I believe this would be a script/ formulaAddress in one Column.JPG

I have created a screen capture.


Thank you.

Lrow = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
 With Sheets(1)
For i = 2 to Lrow
 .Range("j2:j" & Lrow).Formula = "=c2&"" - ""&d2&"" - ""&e2&"" - ""&f2"
.Columns(10).AutoFit
 End With

Hello. and thanks for your help. I went to run that script and received an error. See screen capture.script error.JPG

No problems here add to a module not the sheet module

Sub address()
Dim lrow As Long
Dim i As Long
lrow = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
 With Sheets(1)
For i = 2 To lrow
 .Range("j2:j" & lrow).Formula = "=c2&"" - ""&d2&"" - ""&e2&"" - ""&f2"
.Columns(10).AutoFit
Next
 End With
End Sub

That didn't work. Are you calling my sheet, Sheets (1) ? Because I have named the sheet. Where do I enter my sheet name in the script?Mod.JPG
I have named my column (J) "Full Address" in cell J1

so might not be sheet 1

with Sheets("Your Sheet Name Here with quotation marks")

I'm not 100% clear on your reply. Let me enter the name in the script to see if it's correct. I have named my sheet Example. See below

Sub address()
Dim lrow As Long
Dim i As Long
lrow = Example.Range("a" & Rows.Count).End(xlUp).Row
With Example
For i = 2 To lrow
.Range("j2:j" & lrow).Formula = "=c2&"" - ""&d2&"" - ""&e2&"" - ""&f2"
.Columns(10).AutoFit
Next
End With
End Sub

Sub address()
Dim lrow As Long
Dim i As Long
With Sheets("Example")
lrow = .Range("a" & Rows.Count).End(xlUp).Row

For i = 2 To lrow
 .Range("j2:j" & lrow).Formula = "=c2&"" - ""&d2&"" - ""&e2&"" - ""&f2"
.Columns(10).AutoFit
Next
 End With
End Sub

would it be hard to create a script and remove the hyphen and add a comma? in this format: 1 National Ave, Cleveland, OH 85623
So a comma after the street, and city

That worked. Except the script added a hyphen in - between - each - address - block. screen shot dashes.JPG

I received an error. error.JPG