Named Range VBA

so for cell range C1: C4, create the named range via VBA
But first locate the leftmost and rightmost cells in row 4

I did this but still getting error

Sub Button1_Click()

Dim fixedHeaderRow As Long


Dim f1, f2 As String

f1 = FirstColumn(ActiveSheet)
f2 = LastColumn(ActiveSheet)

'ActiveWorkbook.Names.Add Name:="myRng", RefersTo:="Sheet1!A2:A" & Range("A2").End(xlDown).Row

ActiveWorkbook.Names.Add Name:="myRng", RefersTo:="Sheet1!" & f1 & ":" f2




End Sub


Private Function FirstColumn(TheWorksheet As Worksheet) As String

If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    FirstColumn = Replace((Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Address), "$", "")
End If

End Function



Private Function LastColumn(TheWorksheet As Worksheet) As String
If WorksheetFunction.CountA(TheWorksheet.Cells) > 0 Then
    LastColumn = Replace((Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Address), "$", "")
End If
End Function

Select allOpen in new window

I am not still getting it, please assist

Hello All,

Thank you for the deferent approaches. :)

techfanatic ‘s code was complete except for he  assumed the left and right cells are C1:C4. On the other hand,, Ssaqib showed the quickest way to locate the left and right cells
 while Aebea iterated through all the cells in the header row. Good to know all the different approaches.

Thanks guys for the platinum help.

Edit: Nevermind, I misread the post.

If you guys are interested
https://www.experts-exchange.com/questions/27839975/Date-Extraction-Incorrect.html

A relevant follow up posted here if you are interested
https://www.experts-exchange.com/questions/27841478/Excel-Named-Range-Create-By-Column-Header-Name.html

Thank you