Forum Discussion
Solved
Conditionally merge cells based on number?
Hi all, I want my cell to be merged like this (see attached photo). What defines my data is that there is always an age, so I would like to have, for example, a formula where once a number en...
Sub merge() Dim i As Long Dim x As Variant Dim result As String Dim k As Long Dim m As Long Range("B:B").Clear m = Range("A" & Rows.Count).End(xlUp).Row k = 3 For i = 3 To m x = Right(Cells(i, 1), 1) If IsNumeric(x) Then Cells(k, 2).Value = Trim(result & " " & Cells(i, 1).Value) k = k + 1 result = "" Else result = Trim(result & " " & Cells(i, 1).Value) End If Next i End SubMaybe with this code. In the attached file you can click the button in cell D2 to run the macro.
Sub merge()
Dim i As Long
Dim x As Variant
Dim result As String
Dim k As Long
Dim m As Long
Range("B:B").Clear
m = Range("A" & Rows.Count).End(xlUp).Row
k = 3
For i = 3 To m
x = Right(Cells(i, 1), 1)
If IsNumeric(x) Then
Cells(k, 2).Value = Trim(result & " " & Cells(i, 1).Value)
k = k + 1
result = ""
Else
result = Trim(result & " " & Cells(i, 1).Value)
End If
Next i
End SubMaybe with this code. In the attached file you can click the button in cell D2 to run the macro.
I am very new to VBA and have never coded myself, so I would love to understand the code. Please see my comments for the first sections so far.
Would love a simple easy-to-understand explanation for the rest if possible! 🙂
Code (my two comments have an ' before them):
Option Explicit
Sub merge_cells_together()
'Name your variables wit the Dim as long, dim the result as a string and dim x as a variant (no specific data type)
Dim i As Long
Dim x As Variant
Dim result As String
Dim k As Long
Dim m As Long
'Clear all of column B
Range("B:B").Clear
m = Range("A" & Rows.Count).End(xlUp).Row
k = 3
For i = 3 To m
x = Right(Cells(i, 1), 1)
If IsNumeric(x) Then
Cells(k, 2).Value = Trim(result & " " & Cells(i, 1).Value)
k = k + 1
result = ""
Else
result = Trim(result & " " & Cells(i, 1).Value)
End If
Next i
End Sub
Would love a simple easy-to-understand explanation for the rest if possible! 🙂
Code (my two comments have an ' before them):
Option Explicit
Sub merge_cells_together()
'Name your variables wit the Dim as long, dim the result as a string and dim x as a variant (no specific data type)
Dim i As Long
Dim x As Variant
Dim result As String
Dim k As Long
Dim m As Long
'Clear all of column B
Range("B:B").Clear
m = Range("A" & Rows.Count).End(xlUp).Row
k = 3
For i = 3 To m
x = Right(Cells(i, 1), 1)
If IsNumeric(x) Then
Cells(k, 2).Value = Trim(result & " " & Cells(i, 1).Value)
k = k + 1
result = ""
Else
result = Trim(result & " " & Cells(i, 1).Value)
End If
Next i
End Sub