Currently, I am referencing a range of cells like this:

Dim LR As Long
    With Sheets("Event_Data")
        On Error Resume Next
        LR = Cells(Rows.Count, "M").End(xlUp).Row
        Range("M8:M" & LR).Replace What:=" ", Replacement:="", Lookat:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End With
    Sheet1.Range("M8:M" & LR).SpecialCells(xlCellTypeConstants).Copy _
        Destination:=Sheet2.Range("B2")
I am now trying to not specifically reference column M because the data I am looking at won't always be in column M. In my data I import to Excel, the column before the data that is in M in this case always has the word "TRIG," so I came up with a way to reference the data without using M...partially. So far I have this code working:

 Dim trig As Integer
    trig = Application.Match("TRIG", Sheet1.Rows(7), 0)
    Dim LR As Long
    With Sheets("Event_Data")
        On Error Resume Next
        LR = Cells(Rows.Count, trig + 1).End(xlUp).Row
        Range("M8:M" & LR).Replace What:=" ", Replacement:="", Lookat:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    End With
    Sheet1.Range("M8:M" & LR).SpecialCells(xlCellTypeConstants).Copy _
        Destination:=Sheet2.Range("B2")
My question is, how do I change
Range("M8:M" & LR)
to a format of calling the cells using the trig variable? I have tried a couple of different things, but no luck yet...Thanks for your help!