Getting rows and columns from range object

[cursemyhandz]

I have some data in a spreadsheet, and have used VBA to create a dynamic range object from this data like so:

Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("A1").CurrentRegion

I am new to VBA, so I don't know exactly what this range object is, but I imagine we have coordinates (x_1, y_1) and (x_2, y_2) which define a rectangle. I have a number x_3 (with x_1 < x_3 < x_2), and I want to extract y_1, x_2 and y_2 from my range object to make a new range object with coordinates (x_3, y_1) and (x_2, y_2).

Thank you! Also, I am quite new to VBA, so any resources for getting to grips with this language would be helpful.

 

[Cubist]

A "Range" object represents a cell, a row, a column, or a rectangular range of cells on a worksheet. It's not exactly defined by coordinates like (x, y) in a Cartesian plane, but rather by the addresses of its top-left and bottom-right cells. It's just like when you select a continuous range on the worksheet.

It would be helpful to share your sample data and desired output.