What VBA code would I use to concatenate cell A2 B2 in cell C2 and then have it Autofill down the column

0 votes

The code below, which I am using to combine LastName and FirstName from columns A and B into column C even though I am a novice at VBA, works for each individual cell. The last row might change each time, so how can I get it to repeat for a range of cells?

The code I used, which works for rows A2 and B2, should continue for rows A3, B3, and so on along the rows, with the last row updating as each report is generated.

Sub ConcatenateStrings()

    Dim StringOne As String
    Dim StringTwo As String
    Dim StringThree As String
        StringOne = Range("A2").Value
        StringTwo = Range("B2").Value
        Range("C2").Value = StringOne & ", " & StringTwo

End Sub
Feb 14, 2023 in Others by narikkadan
• 63,700 points
734 views

1 answer to this question.

0 votes

Solution

  1. Find the last row.
  2. Write a formula to concatenate columns B and C.
  3. Hard-code the formula results.
Sub Tester()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
    With ws.Range("C2:C" & lastRow)
        .Formula = "=A2&"", ""&B2"
        .Value = .Value
    End With
End Sub
answered Feb 14, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

What would I have to add to this VBA Code to keep Undo Button working after it runs?

VBA eliminates Excel's undo function. Though it ...READ MORE

answered Dec 16, 2022 in Others by narikkadan
• 63,700 points
303 views
0 votes
2 answers
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,700 points
778 views
0 votes
1 answer

VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,700 points
1,595 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
980 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,331 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
637 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
854 views
0 votes
1 answer
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
907 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP