I have a table created from List of data. How to find the header text of each column
When I select the activecell's header is high lighted to orange but I want to retrieve that value using visual basic. I am able to find excel sheet's address but I need table's column header
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox Target.Value
MsgBox ActiveCell.Address
End Sub
This will return the column header, if the passed cell is in a table
Function TableHeader(cl As Range) As Variant
Dim lst As ListObject
Dim strHeading As String
Set lst = cl.ListObject
If Not lst Is Nothing Then
TableHeader = lst.HeaderRowRange.Cells(1, cl.Column - lst.Range.Column + 1).Value
Else
TableHeader = ""
End If
End Function
I was online searching for a good way to find the header value of any given cell in the table and after a little brainstorming came up with this working solution that I thought I would share.
Target.Offset(1 - Target.Row).Value
In the event that your headers aren't in row 1 then change the 1 to whatever row they happen to be in.
strCurrentColName = Cells(ActiveCell.ListObject.Range.Row, ActiveCell.Column).Value
If by column header you mean the cell in the first row in the same column that a change has been made then you can construct the header using Cells, ie
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
MsgBox Target.Value
MsgBox ActiveCell.Address & vbNewLine & "Column header is " & Cells(1, Target.Column)
End Sub
If your header was in row 2 you would use Cells(2, ActiveCell.Column) etc
[Updated after comment below]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng1 As Range
Set rng1 = Target.End(xlUp)
MsgBox "First value before a blank cell/top of sheet is " & rng1.Value
End Sub
