Read multiple rows as one string in vba

[Nothnless]

Hi, how can I combine multiple rows of text into a single string without concatenating them on the sheet?

row 1 text

row 2 text

row 3 text

row 4 text

I'm trying to do something like this:

Sub Macro1()
Dim myWorkbook As Workbook
Dim mySheet As Worksheet
Dim lngRows As Long
Dim rngRows As Range
Dim mCode As String

Set myWorkbook = ThisWorkbook
Set mySheet = myWorkbook.Sheets("The Sheet Name")
 lngRows = mySheet.Range("B1").Value  'Contains dynamic number of rows
Set rngRows = mySheet.Rows(1 & ":" & lngRows).Cells
 mCode = rngRows.Value

myWorkbook.Queries.Item("Table Name").Formula = mCode

End Sub

 

[Nothnless]

I agree, but it seemed odd to me to concatenate bottom upward... I thought maybe I missed something in the original question that led you to assume that. If we concatenate "normally" (downward)...

Sub ConcatenateColumnRange()

  Dim ConcatenatedText As String

  ConcatenatedText = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))))

  MsgBox ConcatenatedText

End Sub

Hi, how can I add in a comma after ever concatenated text using this code? Sorry, just discovering I need them.

 

[Fluff]

Try

ConcatenatedText = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))),",")

 

[Nothnless]

Try

ConcatenatedText = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))),",")

THANKS!!

 

[Fluff]

You're welcome.