Hi,
I was wondering if someone could help me with the following query please?
I would like to concatenate each set of data between the blank cells and return the value in the next column in line with the final row of data before the blank.
I have found a piece of code and modified it slightly but it is not doing exactly what I want, instead it returns just one concatenate of all the data in a predetermined row.
Thanks in advance,
Setanta
VB Code
Current result
Desired Result
I was wondering if someone could help me with the following query please?
I would like to concatenate each set of data between the blank cells and return the value in the next column in line with the final row of data before the blank.
I have found a piece of code and modified it slightly but it is not doing exactly what I want, instead it returns just one concatenate of all the data in a predetermined row.
Thanks in advance,
Setanta
VB Code
Code:
Option Explicit
Sub ConcatRangeNoBlanks()
Dim c As Range, rng As Range
Application.ScreenUpdating = False
Set rng = Range("A1:A100")
For Each c In rng
If c > 0 Then
Cells(6, 2) = Cells(6, 2) & " " & c
End If
Next c
Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub
Current result
A | B |
1 | |
2 | |
3 | 123456 |
4 | |
5 | |
6 | |
Desired Result
A | B |
1 | |
2 | |
3 | 123 |
4 | |
5 | |
6 | 456 |
Last edited by a moderator: