Referring to a header range even after columns are inserted

Question

I have a spreadsheet as illustrated below: .

The button with the green text allows me to expand and collapse columns A:F, according to the macro below.

The problem is that the macro refers specifically to columns A:F. If I were to insert an additional column under my main column header ("Läkemedelsinformation"), I would still only be able to collapse/expand columns A:F, unless I edit the code manually.

How do I go about making the macro detect the relevant columns dynamically based on the main column header?

Public Sub LKMinfo()
 Dim SH As Worksheet
 Dim Rng As Range
 Dim obj As Variant
 Dim BTN As Button
 Dim iLen As Long
 Const myColumns As String = "A:F"               '<<===== Change
 Const släkemedelsinformation As String = "Läkemedelsinformation"                       '<<===== Change
 Const sHidden As String = " Hidden"
 Const sVisible As String = " Visible"

 Set SH = ActiveSheet
 Set BTN = SH.Buttons(Application.Caller)
 Set Rng = SH.Columns(myColumns)
 With Rng.EntireColumn
     .Hidden = Not .Hidden
     If .Hidden Then
     iLen = Len(sHidden) + Len(släkemedelsinformation)
         BTN.Characters.Text = släkemedelsinformation & " Hidden"
         With BTN.Characters(Start:=1, Length:=iLen).Font
             .Name = "Arial"
             .FontStyle = "Bold"
             .Size = 10
             .ColorIndex = 3  '\\ RED
         End With
     Else
     iLen = Len(sVisible) + Len(släkemedelsinformation)
     BTN.Characters.Text = släkemedelsinformation & " Visible"
         With BTN.Characters(Start:=1, Length:=iLen).Font
             .Name = "Arial"
             .FontStyle = "Bold"
             .Size = 10
             .ColorIndex = 4  '\\ GREEN
         End With
     End If
 End With

 End Sub

Answer 1

Just name your range.

In the example below, I have three headers, and named that range "Barn" using the Name Box (that little field to the left of the function bar). For more help with this, see e.g. this video or any of great many found using a simple web search.

If I then insert a column after column B, the named range "Barn" just expands to include it. This is illustrated when I select the "Barn" range using the Name Box drop-down:

You can refer to this range in VBA as follows:

Dim r As Range
Set r = Range("Barn")

With r.EntireColumn
    '... do stuff

So you don't need to specify which columns Range r should refer to anymore (e.g. A to F — don't need to specify that). You just say that r refers to the range named "Barn".

Answer 2

You can find the columns using the .Find method, passing in the column header name.

I would advise to set the .Find to a range variable.

Dim col_header1 As Range, col_header2 As Range

Set col_header1 = Columns("A:Z").Find("header_name1")
Set col_header2 = Columns("A:Z").Find("header_name2")

You can then assess if you were successful in finding all of the headers. If all of the headers were found, you can assign the column number values and use them in your code.

If Not col_header1 Is Nothing And _
   Not col_header2 Is Nothing Then

    col_number1 = col_header1.Column
    col_number2 = col_header2.Column

Else

    MsgBox ("One or more of the columns were not found!")
    Exit Sub

End If