Combine and copy user selected columns

Richie70

New Member
Joined
Jan 20, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,

I'm having trouble with this and my noob brain can't figure it out.

I want my macro to look for two columns, Part and Rev. Then combine the values with "_" in between and put the result in a new column and copy the formula to the last row.
This code does what i want but only if i hard code the columns - instead of using the user selected one.

If you can find a few mins to help me fix this I'd be very grateful

VBA Code:
Sub macro6() 'posted to MrExcel for help
    Dim ws As Worksheet
    Dim aCell As Range, aRng As Range
    Dim bCell As Range, bRng As Range
    Dim aCol As Long, alRow As Long
    Dim bCol As Long, blRow As Long
    Dim aColName As String
    Dim bColName As String

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        Set aCell = .Range("A1:ZZ1").Find(What:="Part", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If Not aCell Is Nothing Then
            Else
            MsgBox "Part Column Not Not Found"
        End If
    End With
   
    With ws
        Set bCell = .Range("A1:ZZ1").Find(What:="Rev", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If Not bCell Is Nothing Then
            Else
            MsgBox "Rev Column Not Not Found"
        End If
    End With

    'get lastrow
    Dim LastRow As Long
    LastRow = Cells(Rows.Count, "A:A").End(xlUp).Row
    'MsgBox LastRow
   
    'Combine & place part# & rev# in col AA
    Range("AA2:AA" & LastRow) = Evaluate("C2&""_""&I2:I" & LastRow)
 
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub macro6() 'posted to MrExcel for help
    Dim ws As Worksheet
    Dim aCell As Range, aRng As Range
    Dim bCell As Range, bRng As Range
    Dim aCol As Long, alRow As Long
    Dim bCol As Long, blRow As Long
    Dim aColName As String
    Dim bColName As String

    '~~> Change this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("sheet1")
    With ws
        Set aCell = .Range("A1:ZZ1").Find(What:="Part", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If aCell Is Nothing Then
           MsgBox "Part Column Not Not Found"
           Exit Sub
        End If
    
        Set bCell = .Range("A1:ZZ1").Find(What:="Rev", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
        '~~> If Found
        If bCell Is Nothing Then
            MsgBox "Rev Column Not Not Found"
            Exit Sub
        End If

    'get lastrow
    Dim LastRow As Long
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    'MsgBox LastRow
   
    'Combine & place part# & rev# in col AA
    .Range("AA2:AA" & LastRow).FormulaR1C1 = "=rc" & aCell.Column & "&""_""&rc" & bCell.Column
   End With
 
End Sub
 
Solution

Richie70

New Member
Joined
Jan 20, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
That sir, is fabulous! Thank you very much indeed.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,798
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,156
Messages
5,629,019
Members
416,359
Latest member
Juena

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
Top