Union Range Macro getting to much the range

[Tim Hanson]

I am trying to get a Union of ranges from a array of header names but I get to much range

If I select the UnionRange only this should NOT be selected

Needs, Relationships, Population served, Services

From the example data

I can not figure out why

Thanks

'UNION RANGES By Header Names
Sub iUnionRange()
Dim rng As Range
Dim a As Variant

    'Get array of Header Names for Union of there Column Ranges
    a = Sheets("Codes").Range("A1:A55").Value2

    Set rng = UnionRange("Nodes", a)
 
    rng.Select
    Debug.Print rng.Address(0, 0, , True)
 
End Sub

Function UnionRange(shtName As String, HN As Variant) As Range
Dim CN
Dim ws As Worksheet
Dim r As Range
Dim LR As Long, i As Long, j As Long

Set ws = ThisWorkbook.Sheets(shtName)

LR = ws.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

ReDim CN(LBound(HN) To UBound(HN))
With ws
  'Get Array of column numbers corresponding to Header names
    For i = LBound(HN) To UBound(HN)
        j = .Rows(1).Find(HN(i, 1)).Column
        CN(i) = j
    Next i

  Set UnionRange = .Range(.Cells(1, CN(LBound(HN))), .Cells(LR, CN(LBound(HN))))

  For i = (LBound(HN) + 1) To UBound(HN)
    If Not IsEmpty(UnionRange) Then
      Set r = .Range(.Cells(1, CN(i)), .Cells(LR, CN(i)))
      Set UnionRange = Union(UnionRange, r)
    End If
  Next i

End With
End Function

 

[NARAYANK991]

Hi Tim ,

The problem was the optional parameters of the Find method.

Narayan

 

[Tim Hanson]

yah I have been burned by forgetting that in the past

Thank you!