CODEWITHSUNDEEP
excelvbaloops
Cristopher Ontiveros
Cristopher Ontiveros

Reputation: 36

Using a VBA For Loop to concatenate column in excel

I have a column of data in excel. I want to loop through the data and combine the contents into a single string. I can specify the cell range, but what if the range is unknown. I want to be able to loop until the cell becomes empty. here is what I have so far. 

Sub ConcatenationLoop()

Dim rng As Range, i As Integer

Set rng = Range("A1", "A5")

For i = 1 To rng.Rows.Count
With Range("B1")
    If .Value = "" Then
        .Value = rng.Range("A" & i)
    Else
        .Value = .Value & ", " & rng.Range("A" & i)
    End If
End With
Next

is it possible to combine with something like:

Do Until IsEmpty(ActiveCell)

Much help is appreciated!

End Sub

Upvotes: 0

Views: 192

Answers (3)

JohnyL
JohnyL

Reputation: 7122

You could use the following skeleton:

Sub ALoop()
    Dim r As Long
    r = 2 '//Start row
    While Len(Cells(r, "A")) > 0 '//Or While Not IsEmpty(...)
        '// Your code
        r = r + 1 '//Don't forget to increment row
    Wend
End Sub

Upvotes: 0

Amen Jlili
Amen Jlili

Reputation: 1934

With Worksheets("YourSheetName")
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Loop it to LastRow.

Upvotes: 0

Paul Kelly
Paul Kelly

Reputation: 985

Get the first empty cell from the top using

lLastRow = sheet.Cells(1, 2).End(xlDown).Row

The use this in your for loop

For i = 1 To lLastRow

Upvotes: 0

Related Questions