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

 

[Michael M]

This assumes the text is in Col "A"

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
Cells(r - 1, 1).Value = Cells(r - 1, 1) & " " & Cells(r, 1).Value
Rows(r).Delete
Next r
End Sub

 

[Nothnless]

This assumes the text is in Col "A"

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
Cells(r - 1, 1).Value = Cells(r - 1, 1) & " " & Cells(r, 1).Value
Rows(r).Delete
Next r
End Sub

Thanks for the effort but this concatenates the rows on the sheet. I can do that, what I'm more asking about is if VBA can read multiple rows as one string variable without having to concatenate them on the sheet.

 

[Michael M]

Ok, maybe this way then

Sub MM1()
Dim lr As Long, r As Long, t As String
lr = Cells(Rows.Count, "A").End(xlUp).Row
t = ""
For r = lr To 1 Step -1
t = t & Cells(r, 1).Value
Next r
MsgBox t
End Sub

 

[Rick Rothstein]

Michael, why are you concatenating the cells in reverse (bottom up)?

 

[Michael M]

Good Point.....but how do we know what the OP actually wants ?
We don't actually know what the strings represent !

 

[Michael M]

Does the OP want spaces between each string OR something else ?

Sub MM1()
Dim lr As Long, r As Long, t As String
lr = Cells(Rows.Count, "A").End(xlUp).Row
t = ""
For r = 1 To lr
t = t  & " " & Cells(r, 1).Value
Next r
MsgBox t
End Sub

 

[Rick Rothstein]

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

 

[Michael M]

I agree Rick, but you didn't miss anything !!

 

[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

Awesome! Thanks so much Rick, you guys are the best.

Sorry Michael, I should have made it clearer what I was trying to actually do with the string but thanks for helping.