Hello:

I'm using the following code to dynamically assign addresses based on
field headers for a report. They are address fields that are broken up
into 5 columns that I concatenate into 1 column. When I use
Intersect(ActiveSheet.UsedRange, Range("B:B, Z:Z")) it works fine, but
if I change to Intersect(ActiveSheet.UsedRange, Range(HAdd, BAdd)) it
treats it as Range(B:Z), how do I change this?

Here's the code!

Sub headername()
Dim MyCell As Range
Dim Rng As Range
Dim LRow As Integer
Dim HAdd As Range
Dim BAdd As Range

LRow = LastRow(ActiveSheet)
Set Rng = Range("A2:FF" & LRow)

'* Dynamically assigns addresses for HAdd and BAdd

'* Set HAdd
Set HAdd = Cells.Find(What:="HomeAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
HAdd = (HAdd.EntireColumn.Address)

'* Set BAdd
Set BAdd =
Cells.Find(What:="BusinessAddressLine1", _
After:=ActiveCell, LookIn:=xlFormulas, _
Lookat:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
BAdd = (BAdd.EntireColumn.Address)

'* Concatenates 5 address columns for Each HAdd and BAdd
For Each MyCell In Intersect(ActiveSheet.UsedRange,
Range(HAdd, BAdd))
MyCell.Formula = MyCell.Value & " " _
& MyCell.Offset(0, 1).Value & " " _
& MyCell.Offset(0, 2).Value & " " _
& MyCell.Offset(0, 3).Value & " " _
& MyCell.Offset(0, 4).Value
MyCell.Formula = LTrim(MyCell.Formula)
MyCell.Formula = RTrim(MyCell.Formula)
Next
End Sub