Simple Concatenation

[PJ]

I want to concatenate the text from cells C, D & E17 into my csz variable.
What I have below works, but do I have to specify the sheet name for each
cell reference or is there a way to shorten this?

csz = Sheets("Sheet1").Range("C17") & Sheets("Sheet1").Range("D17") &
Sheets("Sheet1").Range("E17")

Thanks

 

[VJ]

Use with statement

with WorkSheetObject
varname = .Cell1 & .Cell2
end with

 

[Dave Peterson]

with worksheets("sheet1")
csz = .Range("C17").value & .Range("D17").value & .Range("E17").value
end with

I like to specify the property I want to use, too. But maybe you'd want
..text???

 

[Jacob Skaria]

Dim ws as Worksheet, csz as String
Set ws = Sheets("Sheet1")
csz = ws.Range("C17") & ws.Range("D17") & ws.Range("E17")
Msgbox csz


If this post helps click Yes

 

[Rick Rothstein]

And, for a different method, which can easily be expanded to as many
contiguous cells in a row (just change the Range)...

Set R = Sheets("Sheet1").Range("C17:E17")
With WorksheetFunction
csz = Join(.Transpose(.Transpose(R)), "")
End With

If you Dim your variables (and you should), Dim csz as a String and R as a
Range. Now, the above code is good for rows; if you had a contiguous range
of cells in a column, then you would need to remove one of the Transpose
function calls (and, with that simplification, we could get rid of the
With..EndWith structure as well...

Set R = Sheets("Sheet1").Range("A10:A99")
csz = Join(WorksheetFunction.Transpose(R), "")

Also, using either of the above methods (depending on if your data is in a
row or in a column), you can easily specify a delimiter to place between
your cell values... just replace the "" with whatever that delimiter should
be (in quotes, of course). For example, to put a comma between the values,
just use "," instead of "".