Range within a Range

[michelle439731]

Morning,

I've got a matrix set up in excel which is a named range.
I want to extract a subset from this matrix and make this a seperate range
that can be called independently. For example my matrix (A1:C3) is named
Range1, I want to code in something that will highligt (B2:C3) and make it a
range called Range2.

The data I want to apply this to is obviously much bigger and the size of
Range2 is gonig to vary across runs of the tool it's coded into.

Please can you help me with the VBA code to set this up.

Thank you!

 

[Patrick Molloy]

use the OFFSET function

Option Explicit

Sub xxx()


Dim target As Range
Set target = Range("range1").Offset(1, 1).Resize(2, 2)
'matricx in memory
' do something
With target
Range("F2").Resize(.Rows.Count, .Columns.Count).Value = .Value
Range("F2").Resize(.Rows.Count, .Columns.Count).Name = "range2"
End With

End Sub

 

[Patrick Molloy]

rather than dumping in to teh sheet, just do this

Target.Name = "range2"

which answers yuor question properly