Referencing Columns based on Header Names in VBA Macros; Excel 2013, Windows 10

minddumps

New Member
Joined
Jun 29, 2011
Messages
16
Good day to you. I'm trying to revamp a bit of VBA code based on unknown column locations, known only by their header names.

:cool:This first bit of code is working properly as it should:
Code:
Private Sub STEP4_Separate_Name()
'
    '''''''''''''''''''''''''''''''''''''''''''''Insert 4 columns to right''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim rngNameHeader As Range
    Dim rngMyNewColumn As Range

    Set rngNameHeader = Range("A1:ZZ1").Find(What:="Name", LookIn:=xlValues, LookAt:=xlWhole, _
                    MatchCase:=False, SearchFormat:=False)
    rngNameHeader.Offset(, 1).Resize(, 4).EntireColumn.Insert Shift:=xlToRight

    'You'll need to check the named range doesn't exist first.
    ThisWorkbook.Names.Add Name:="MyNewRange", _
        RefersTo:="='" & rngNameHeader.Parent.Name & "'!" & _
                         rngNameHeader.Offset(1, 1).Address

    Set rngMyNewColumn = Range("MyNewRange")
'    MsgBox rngMyNewColumn.Address
    
    '''''''''''''''''''''''''''''''''''''''''''''Insert "Proper" formula to first column on the right''''''''''''''''''''''''''''''''''''''''''''''''''''
        'NOTE: This is what my recorded macro shows, but the Column to select might not always be G'''
            'Range("G2").Select
            'ActiveCell.FormulaR1C1 = "=PROPER(RC[-1])"
            'Range("G2", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
                   
    rngMyNewColumn.FormulaR1C1 = "=PROPER(RC[-1])" 'This works properly and inserts the formula in the first column after "Name" column and second row

:confused:This is where I'm struggling; I've added notes to what each column section should reference:
Code:
        Range(rngMyNewColumn, [Code to find Column Letter here] & Cells(Rows.Count, 1).End(xlUp).Row).FillDown 'I've tried so many different things for this line to no avail.
        
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''''''''Select same column with formulas to paste values'''''''''''''''''''''''''''''''''''''''''''''
    Columns("G:G").Select ' This should be the first column after "Name" that has all the formulas copied down each row
    Selection.Copy
    Columns("F:F").Select 'This should be the "Name" column to paste the new "Proper" Names
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select 'This should select the original "Name" Column, though the header will now be blank
    Application.CutCopyMode = False
    Application.DisplayAlerts = False
    
    '''''''''''''''''''''''''''''''''''''''''''''Seperate Names to concatenate to the columns to the right'''''''''''''''''''''''''''''''''''''''''''''
        Selection.TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
        Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True 'This is from the Text to Columns selection on the Data Tools Tab from the Data Menu Item
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Last Name"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "First Name"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "MI"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Full Name"
            Columns("J:J").Select
            Application.CutCopyMode = False
            Selection.Delete Shift:=xlToLeft
    Range("I2").Select 'Column Header Full Name
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(RC[1],"" "",RC[-2],"" "",RC[-1],"" "",RC[-3])" 'I need to concatenate existing Column Header Row "Rank", Column Header "First Name", "MI", "Last Name"; RC will work for the newly created columns, but not for the Rank column--that will have to be searched for and placed in the formula
            Range("I2", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
    Columns("I:I").Select 'This is the column that the concatenate formula was written into
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    

End Sub

    
 End Sub


I'd greatly appreciate some help on figuring out how to find unknown columns and tell excel how to find them.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
The first part of your code establishes a single-cell named range "MyNewRange". You could change that to be the entire column with the header "Name" by changing one line like this (change is in bold red font):

Rich (BB code):
ThisWorkbook.Names.Add Name:="MyNewRange", _
        RefersTo:="='" & rngNameHeader.Parent.Name & "'!" & _
                         rngNameHeader.Offset(1, 1).EntireColumn.Address
and use that column as the starting point to locate other columns, avoiding having to use column letters.
 
Upvote 0
Thank you for that feedback, one issue I see with doing that however, is it makes my file so much larger because the formula pastes down to rows where I have no content as well.

Any other suggestions by chance? I'm a bit of a novice when it comes to this bc I copy and paste and then edit the code how I need it LOL...
 
Upvote 0
Google or search this forum for how to determine the last row using VBA for Excel. Then you can use that row to limit the range where you paste the formula.
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

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