VBA code to concatenate cell only that have value

sreejesh

New Member
Joined
Oct 3, 2009
Messages
49
Hi,

I need help to only concatenate cells that have value in 1 particular row, with another.

Sno.ItemsID
1 12345
2 apple2345
3 apple5578
4 74458
5 54785
6 orange58879
7 66524
8 pineapple258486
9 5852452
10 2554757

<colgroup><col><col><col></colgroup><tbody>
</tbody>

to

Sno.ItemsID
1 12345
2 apple23452345
3 apple55785578
4 74458
5 54785
6 orange5887958879
7 66524
8 pineapple258486258486
9 5852452
10 2554757

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Maybe something along these lines:-
Code:
Option Explicit
'
Public Sub ConcatenateIf()
'
  Dim ws As Worksheet
  Dim iLast As Long
  Dim iRow As Long
'
  Set ws = ThisWorkbook.[B][COLOR=#ff0000]Sheets(1)[/COLOR][/B]
'
  iLast = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
'
  For iRow = 1 To iLast
    If Not IsEmpty(ws.Cells(iRow, "A")) Then ws.Cells(iRow, "A") = ws.Cells(iRow, "A") & ws.Cells(iRow, "B")
  Next iRow
'
End Sub

You need to ckeck that the bit in red points to the worksheet you want to process. Please make a backup of your workbook before testing.
 
Last edited:
Upvote 0
it did half the job, it merged the header and even the empty cells
Sno.s ItemsID ID
1 12345 12345
2 apple2345 2345
3 apple5578 5578
4 74458 74458
5 54785 54785
6 orange58879 58879
7 66524 66524
8 pineapple258486 258486
9 5852452 5852452
10 2554757 2554757
 
Upvote 0
Sno.s ItemsID ID
1 12345 12345
2 apple2345 2345
3 apple5578 5578
4 74458 74458
5 54785 54785
6 orange58879 58879
7 66524 66524
8 pineapple258486 258486
9 5852452 5852452
10 2554757 2554757
 
Upvote 0
Sorry, are you showing us cols A-C, with Sno in col A?
 
Upvote 0
My fault - I misunderstood. Try:-
Code:
Option Explicit
'
Public Sub ConcatenateIf()
'
  Dim ws As Worksheet
  Dim iLast As Long
  Dim iRow As Long
'
  Set ws = ThisWorkbook.Sheets(1)
'
  iLast = ws.Cells(ws.Rows.Count, "[COLOR=#FF0000][B]A[/B][/COLOR][COLOR=#000000]"[/COLOR]).End(xlUp).Row
'
  For iRow =[COLOR=#FF0000][B] 2[/B][/COLOR] T[COLOR=#FF0000][/COLOR][COLOR=#000000]o[/COLOR] iLast
    If Not IsEmpty(ws.Cells(iRow, "[COLOR=#FF0000][B]B[/B][/COLOR]")) Then ws.Cells(iRow, "[B][COLOR=#FF0000]B[/COLOR][/B]") = ws.Cells(iRow, "[B][COLOR=#FF0000]B[/COLOR][/B]") & ws.Cells(iRow, "[B][COLOR=#FF0000]C[/COLOR][/B]")
  Next iRow
'
End Sub
 
Upvote 0
Hi,

I need help to only concatenate cells that have value in 1 particular row, with another.

Sno.
Items
ID
1
12345
2
apple
2345
3
apple
5578
4
74458
5
54785
6
orange
58879
7
66524
8
pineapple
258486
9
5852452
10
2554757

<TBODY>
</TBODY>

to

Sno.
Items
ID
1
12345
2
apple2345
2345
3
apple5578
5578
4
74458
5
54785
6
orange58879
58879
7
66524
8
pineapple258486
258486
9
5852452
10
2554757

<TBODY>
</TBODY>
Give this macro a try...
Code:
Sub CombineCellsWithValues()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  Range("B2:B" & LastRow) = Evaluate("IF(LEN(B2:B" & LastRow & "),B2:B" & LastRow & "&C2:C" & LastRow & ","""")")
End Sub
 
Upvote 0
Ruddles code works fine for me
So I'd suggest then that the cells in Col "B" only appear blank, but in actual fact have something in them....
To check this in a cell somewhere else try this formula using a blank cell in "B" as the reference, like
Code:
=Len(B2)
The result should be zero, if it's not then the cells aren't blank
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top