How can I use part of a given range in vba (excel) i.e. certain rows in the range
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
Comments
-
Fletch almost 2 years
I have a range that references a table in excel. I need to create a graph and therefore set the source data using my table range. I want the source data to be the first row and last 2 rows from my table (the headings and 2 total rows). Given that the range is formed using variables, is there a way of selecting those sections of any given range (assuming the range consists of three or more rows). Here's my code so far:
Sub addchart(ByVal TableRange As Range, SheetName As Worksheet, TblLabel As String, TableLabel As String) Dim ChtPosition As Range Dim ChtRow As Long Dim ChtSourceData As Range Set ChtSourceData = Union(Top row of TableRange here, Last 2 rows of TableRange here) ChtRow = SheetName.Cells(SheetName.Rows.Count, "B").End(xlUp).Row + 2 ChtPosition = SheetName.Cells(ChtRow, 2) SheetName.Shapes.addchart.Select With ActiveChart .SetSourceData Source:=SheetName.Range(ChtSourceData) .ApplyChartTemplate ("\\graphtemplatefilepath") .Parent.Name = "Cht" & TblLabel End With With SheetName.ChartObjects("Cht" & TblLabel) .Width = (16 * 29) .Height = (7 * 29) .Left = ChtPosition.Left .Top = ChtPosition.Top .Chart.ChartTitle.Characters.Text = TableLabel & " by Month" End With End Sub
-
Fletch about 11 yearsSiddharth, thank you. This code worked well for me and it was great to have such a quick, well thought out response.
-
Siddharth Rout about 11 years@Fletch: Glad to be of help :)