Selecting a variable range

  • Thread starter Thread starter Colin Foster
  • Start date Start date
C

Colin Foster

Hi Group,
I'm trying to select a range using VBA which is dependent on the values held
in two named ranges within the main workbook.
These named ranges are "Rows" and "Columns". The idea is that starting from
a defined startpoint (Cell C4), the VBA allows a selection from C4 to, for
example E6. To allow me to do this, I've tried using the R1C1 format and
come up with the following...
ActiveWorkbook.Range(RefersToR1C1:="R4C3" & ":R" & "rows" & "C" &
"Columns").Select
As I guess you realise due to my posting here, it doesn't work. In the above
example, the value in "Rows" would be 6 and "Columns" would be 5 (to give
E6).

Any suggestions would be welcome.
Regards
Colin Foster
 
Hi
try something like

sub foo()
dim row_no as long
dim col_no as integer
dim rng as range

with activesheet
row_no=.range("A1").value
col_no = .range("B1").value
set rng = range(cells(4,3),cells(row_no,col_no))
msgbox rng.address
end with
end sub
 
You are ending up with the string "R4C3:RrowsCcolumns". Not what you want. You
haven't told VBA that Rows and Columns are the names of ranges. As written,
they are just literal text. You would need to write

":R" & Range("Rows").Value & "C" & Range("Columns").Value

But there are additional problems.

1. re RefersToR1C1: this is a property of a Name object, not of a workbook.
From Help: "Returns or sets the formula that the name refers to."

2. Workbooks don't have Range properties; Worksheets do.

Anyway, I would write it this way. A bit more verbose, but I think easier to
read:

With ActiveWorkbook.ActiveSheet
R = .Range("Rows").Value
C = .Range("Columns").Value
.Range("C4", .Cells(R, C)).Select
End With

You may not need to select anything at all. What does your code do next?
 
Sorted!!!
Basically, once the range is created/selected, then a format is applied to
it. Basically this will save loads of time in creating lots of grids!
Thanks Myrna (and Frank) for your suggestions
Regards
Colin Foster
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top