Concatinate random cell with | parameter in excel VBA

April 17, 2017 at 00:53:47
Specs: Windows 7, Dell
I have column A and B of random rows which has to be concatinated
For Example
Col A Col B
abc 100
edf 200

Number of rows will vary from file to file.
I have generated a code to concatenate Col A and Col B Like
Col A Col B Combine A & B
abc 100 abc=100
edf 200 edf=200

My issue is after combining all the Col A and Col B and store in COL C I want to get result Like

Col A Col B Combine A & B Final Value
abc 100 abc=100 abc=100|edf=200
edf 200 edf=200

I want to append Combine A & B (COL 3) with | symbol .

Note: Rows may vary ...I have written code upto Combining A&B lst step i am getting error.

######################################################################
Code:


Sub COMBINE()
'
' COMBINE Macro
'
Dim lastRow As Long
'Find the last non-blank cell in column A(1)
A_Lastrow = Range("A" & Rows.Count).End(xlUp).Row
B_Lastrow = Range("B" & Rows.Count).End(xlUp).Row

If A_Lastrow > B_Lastrow Then lastRow = A_Lastrow Else lastRow = B_Lastrow

MsgBox "Last Row: " & A_Lastrow & vbNewLine & _
"Last Column: " & B_Lastrow
'Inserting a Column after Column B
Range("C1").EntireColumn.Insert

'ACTIVATE THE ACTIVE CELL TO C1
Application.GoTo Sheet1.Range("C1"), True

' DEFAULT THE COLUMN NAME AS COMBINE MACRO
ActiveCell.FormulaR1C1 = "COMBINE COLUMN A & B"

'SELECT THE C2 CELL AND APPLY THE FORMULA TO COMBINE AND AUTO FILL THE FORMULA
Range("C2").Select
Range("C2:C" & lastRow).Formula = "=(RC[-2]&""=""&RC[-1])"
Application.GoTo Sheet1.Range("C1"), False

End Sub
Sub FINAL()
'
' Macro4 Macro
'

'
Call COMBINE
Application.GoTo Sheet1.Range("D1"), True

End Sub

##########################################################################


See More: Concatinate random cell with | parameter in excel VBA

Reply ↓  Report •


#1
April 19, 2017 at 06:59:29
To be able to help you with this you will need to present us with example data using the PRE tags, please see the link below on how to use pre tags

https://www.computing.net/howtos/sh...

please use column headers and row numbers

The example data should show a before and after so we can clearly see what you want the code to do.

Provide this and we can help


Reply ↓  Report •
Related Solutions


Ask Question