VBA to find my headers and use them instead of range

[Vivelake]

Hi,

I have a spreadsheet where the columns changed at times (more are added or some are deleted). It's too much work to edit the ranges in all my macros every time. Is there a way for the VBA to figure out what column Q certain header is in and adjust the letters in the range accordingly? I need quite a simple solution to this.

 

[gordsky]

If the header name remains consistent then have VBA search for it and set the column number/letter from that result

 

[mikerickson]

You could write a function that returns the column that has a specified header

Function NamesDataRange() as Range
    With Sheets("Sheet1")
        Set NamesDataRange = .Columns(Application.Match("Name", .Rows(1), 0)).EntireColumn
    End With
End Function

And use NamesDataRange throughout your code as you would a range variable. Or set a variable to its value.

 

[Vivelake]

You could write a function that returns the column that has a specified header

Function NamesDataRange() as Range
    With Sheets("Sheet1")
        Set NamesDataRange = .Columns(Application.Match("Name", .Rows(1), 0)).EntireColumn
    End With
End Function

And use NamesDataRange throughout your code as you would a range variable. Or set a variable to its value.

How would that work if I for example wanted to select the column that had "Apple" as a header, and highlight it in yellow or something like that?

 

[mikerickson]

You would first rewrite NamesDataRange, either as a hardcoded AppleDataRange function or as a function that takes a header as an argument.

Then the code turning things yellow would be AppleDataRange.Color = vbYellow

Or HeaderDataRange("apple").Color = vbYellow depending on how you wanted to alter the UDF.