CODEWITHSUNDEEP
excelvba
lakshmen
lakshmen

Reputation: 29064

Select cells to the last filled row in a named range VBA

I have a named range, for example GroupedCells: Sheet1$C$1,Sheet1$D$1,Sheet1$G$1. I would like to get the data in these rows to the last filled row.

Code:

Sheets("Sheet1").Select
Range("GroupedCells").Select
Range(Selection, Selection.End(xlDown)).Select

It doesn't work as it selects only Sheet1$C$1 to Sheet1$C$10 for example. I need it to select Sheet1$C$1: Sheet1$C$10, Sheet1$D$1: Sheet1$D$10, Sheet1$G$1: Sheet1$G$10. How do i do it using Namedrange.

Upvotes: 1

Views: 383

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please:

Sub testSelectNamedRange()
      Dim rng As Range, lastRow As Long, rng1 As Range
      Dim rng2 As Range, rng3 As Range, arrRng As Variant
       Set rng = Range("GroupedCells")  'put the named range in a variable
       arrRng = Split(rng.address, ",") 'extract each cell addres from the above range
       lastRow = Cells(Rows.count, rng.Column).End(xlUp).Row 'determine the last row of the first rng column
       Set rng1 = Range(Range(arrRng(0)), Cells(lastRow, Range(arrRng(0)).Column)) 'create a range for each column
       Set rng2 = Range(Range(arrRng(1)), Cells(lastRow, Range(arrRng(1)).Column))
       Set rng3 = Range(Range(arrRng(2)), Cells(lastRow, Range(arrRng(2)).Column))
       Union(rng1, rng2, rng3).Select 'select the union of the above created ranges
End Sub

Upvotes: 1

Dy.Lee
Dy.Lee

Reputation: 7567

Try,

Sub test2()
    Dim rngDB As Range
    Dim rngU As Range
    Dim ar As Range
    Set rngDB = Range("GroupedCells")

    For Each ar In rngDB.Areas
        If rngU Is Nothing Then
            Set rngU = Range(ar, ar.End(xlDown))
        Else
            Set rngU = Union(rngU, Range(ar, ar.End(xlDown)))
        End If
    Next
    If rngU Is Nothing Then
    Else
        rngU.Select
    End If
End Sub

Upvotes: 2

Related Questions