Range2Block

Concatenate all rows found in a range starting A1Cell for Columns_To columns into 1 textblock with separator that can be changed.
Concatenation will be columns then rows, example if A1Cell="A1", Columns_To=4, and that range has 5 rows, it will be
A1 | B1 | C1 | D1 | A2 | B2 | C2 | D2 | etc ...

Different versions: ANStr2Range and Range2ANStr

CodeFunctionName

What is this?

Public

Tested

Original Work

Function Range2Block(A1Cell, Optional Columns_To = 1, Optional InSheet = "This", Optional InWB = "This", Optional Sepa = vbTab)
    ' Concatenate all rows found in a range starting A1Cell for Columns_To columns into 1 textblock with separator that can be changed
    '    Concatenation will be columns then rows, example if A1Cell="A1", Columns_To=4, and that range has 5 rows, it will be
    '        A1 | B1 | C1 | D1 | A2 | B2 | C2 | D2 | etc ...
    '
    If InWB = "This" Then InWB = ThisWorkbook.Name
    If InSheet = "This" Then InSheet = Workbooks(InWB).ActiveSheet.Name
    Rett = ""
    Rett = Rett & ""
    X1 = 1
    Max1 = CountColumnCells(GetColumnName(A1Cell), , ShD.Name)
    Do Until X1 > Max1
        If Rett > "" Then Rett = Rett & vbCrLf
        For I = 1 To Columns_To
            Rett = Rett & Workbooks(InWB).Worksheets(InSheet).Range(A1Cell).Offset(X1 - 1, I - 1).Value
            Rett = Rett & Sepa
        Next
        X1 = X1 + 1
    Loop
    Range2Block = Rett
End Function

A1Cell, Optional Columns_To = 1, Optional InSheet = "This", Optional InWB = "This", Optional Sepa = vbTab

Concatenation will be columns then rows, example if A1Cell="A1", Columns_To=4, and that range has 5 rows, it will be
A1 | B1 | C1 | D1 | A2 | B2 | C2 | D2 | etc ...