CODEWITHSUNDEEP
excelvba
adayzdone
adayzdone

Reputation: 11238

Range of cells into single cell with carriage return

I am working through my first VBA book and would appreciate if someone would point me in the right direction. How would I transfer a range of rows into a single cell with carriage returns? I would then like to repeat this action for all ranges in the column.

I think I need to:

Start

enter image description here

Upvotes: 2

Views: 1892

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Following up on my comments. here is a very simple way to achieve what you want.

Option Explicit

'~~> You can use any delimiter that you want
Const Delim = vbNewLine

Sub Sample()
    Dim rngInput As Range, rngOutput As Range

    Application.ScreenUpdating = False

    Set rngInput = Range("A1:A5") '<~~ Input Range
    Set rngOutput = Range("B1")   '<~~ Output Range

    Concatenate rngInput, rngOutput

    Application.ScreenUpdating = True
End Sub

Sub Concatenate(rng1 As Range, rng2 As Range)
    Dim cl As Range
    Dim strOutPut As String

    For Each cl In rng1
        If strOutPut = "" Then
            strOutPut = cl.Value
        Else
            strOutPut = strOutPut & Delim & cl.Value
        End If
    Next

    rng2.Value = strOutPut
End Sub

Upvotes: 2

Jay
Jay

Reputation: 57919

Within the context of a worksheet-level code, the following will work. Column 2 is hard-coded, so you might want to pass in a value or otherwise modify it to fit your needs.

Dim rng As Range
Set rng = Me.Columns(2)

Dim row As Integer
row = 1

' Find first row with non-empty cell; bail out if first 100 rows empty
If IsEmpty(Me.Cells(1, 2)) Then
    Do
        row = row + 1
    Loop Until IsEmpty(Me.Cells(row, 2)) = False Or row = 101
End If

If row = 101 Then Exit Sub

' We'll need to know the top row of the range later, so hold the value
Dim firstRow As Integer
firstRow = row

' Combine the text from each subsequent row until an empty cell is encountered
Dim result As String
Do
    If result <> "" Then result = result & vbNewLine
    result = result & Me.Cells(row, 2).Text
    row = row + 1
Loop Until IsEmpty(Me.Cells(row, 2))

' Clear the content of the range
Set rng = Me.Range(Me.Cells(firstRow, 2), Me.Cells(row, 2))
rng.Clear

' Set the text in the first cell
Me.Cells(firstRow, 2).Value2 = result

Upvotes: 1

Related Questions