You were on the right track. Here's two version, since it wasn't clear if you want the last column+6, or seven before but not inclusing last col
Option Explicit
Sub drv()
MsgBox Last7.Address
MsgBox Last7Before.Address
End Sub
Function Last7() As Range
With ActiveSheet
Set Last7 = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, -6).Resize(1, 7).EntireColumn
End With
End Function
Function Last7Before() As Range
With ActiveSheet
Set Last7Before = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, -7).Resize(1, 7).EntireColumn
End With
End Function