CODEWITHSUNDEEP
excelvba
Amy
Amy

Reputation: 21

How to combine 2 cell value into one cell with VBA?

enter image description here

How to use VBA combine Col A and B together with ";",and remove Col A and B after combined,

enter image description here

Upvotes: 1

Views: 1306

Answers (2)

VBasic2008
VBasic2008

Reputation: 54807

Concatenate Columns

Option Explicit

Sub ConcatColumns()
    
    Const wsName As String = "Sheet1"
    Const Cols As String = "A:B"
    Const Col As String = "A"
    Const fRow As Long = 2
    Const Delimiter As String = ";"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    If lRow < fRow Then Exit Sub ' no data
    Dim rCount As Long: rCount = lRow - fRow + 1
    Dim rg As Range: Set rg = ws.Rows(fRow).Columns(Cols).Resize(rCount)
        
    rg.Columns(1).Value = Evaluate(rg.Address(0, 0) & "&""" _
        & Delimiter & """&" & rg.Columns(2).Address(0, 0))
    
    rg.Columns(2).ClearContents
    
End Sub

Upvotes: 0

Harun24hr
Harun24hr

Reputation: 36870

Try below sub-

Sub MergeCells()
Dim rng As Range
Dim LastRow As Long

  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
  For Each rng In Range("A2:A" & LastRow)
    rng = rng & ";" & rng.Offset(0, 1)
    rng.Offset(0, 1).Clear
  Next

End Sub

Upvotes: 1

Related Questions