How can I use part of a given range in vba (excel) i.e. certain rows in the range

charts, excel, range, vba

Solution

The logic is to find the First Row, First Column, Total Number of rows, Total Number of columns and then deduce the range that you want. Let me explain it with an example. I have commented the code so that you shouldn't have any problem understanding it.

Sub Sample()
    Dim TestRange As Range
    Dim rng1 As Range, rng2 As Range, rng3 As Range, FinalRange As Range
    Dim ws As Worksheet
    Dim r As Long, c As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set TestRange = .Range("A3:B10")

        Debug.Print "Our Sample Range is " & TestRange.Address

        '~~> This will give you 8
        Debug.Print "Total Rows in the range is " & TestRange.Rows.Count

        '~~> This will give you 3 From A3
        Debug.Print "The First Row in the range is " & TestRange.Row

        '~~> This will give you 1 From A3
        Debug.Print "The First Column in the range is " & TestRange.Column

        r = TestRange.Row
        c = TestRange.Column

        '~~> This will give you 2
        Debug.Print "Total Columns in the range is " & TestRange.Columns.Count
        ColCount = TestRange.Columns.Count

        '~~> This is give you the 1st row in that range $A$3:$B$3
        Debug.Print "The First Row address is " & Range(.Cells(r, c), _
        .Cells(r, c + ColCount - 1)).Address
        Set rng1 = Range(.Cells(r, c), .Cells(r, c + ColCount - 1))

        '~~> This will give you the last row
        Debug.Print "The Last Row address is " & _
        Range(.Cells(r + TestRange.Rows.Count - 1, c), _
        .Cells(r + TestRange.Rows.Count - 1, c + ColCount - 1)).Address

        Set rng2 = Range(.Cells(r + TestRange.Rows.Count - 1, c), _
        .Cells(r + TestRange.Rows.Count - 1, c + ColCount - 1))

        '~~> This will give you the Second last row
        Debug.Print "The Second Last Row address is " & _
        Range(.Cells(r + TestRange.Rows.Count - 2, c), _
        .Cells(r + TestRange.Rows.Count - 2, c + ColCount - 1)).Address

        Set rng3 = Range(.Cells(r + TestRange.Rows.Count - 2, c), _
        .Cells(r + TestRange.Rows.Count - 2, c + ColCount - 1))

        '~~> This will give you the final range i.e $A$3:$B$3,$A$9:$B$10
        Set FinalRange = Union(rng1, rng2, rng3)
        Debug.Print "The Final Range address is " & FinalRange.Address
    End With
End Sub

When you run the above code you get this output in the Immediate Window

Our Sample Range is $A$3:$B$10
Total Rows in the range is 8
The First Row in the range is 3
The First Column in the range is 1
Total Columns in the range is 2
The First Row address is $A$3:$B$3
The Last Row address is $A$10:$B$10
The Second Last Row address is $A$9:$B$9
The Final Range address is $A$3:$B$3,$A$9:$B$10

EDIT

So the above code minus the debug statements and proper variable declaration can be written as

Sub Sample()
    Dim TestRange As Range
    Dim rng1 As Range, rng2 As Range, rng3 As Range, FinalRange As Range
    Dim ws As Worksheet
    Dim r1 As Long, c1 As Long, rCount As Long, cCount As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        Set TestRange = .Range("A3:B10")

        rCount = TestRange.Rows.Count
        r1 = TestRange.Row

        cCount = TestRange.Columns.Count
        c1 = TestRange.Column

        Set rng1 = Range(.Cells(r1, c1), .Cells(r1, c1 + cCount - 1))

        Set rng2 = Range(.Cells(r1 + rCount - 1, c1), _
        .Cells(r1 + rCount - 1, c1 + cCount - 1))

        Set rng3 = Range(.Cells(r1 + rCount - 2, c1), _
        .Cells(r1 + rCount - 2, c1 + cCount - 1))

        Set FinalRange = Union(rng1, rng2, rng3)

        Debug.Print "The Final Range address is " & FinalRange.Address
    End With
End Sub

Problem

Original source