variable range definition

J

jmp

how do I define a variable range that can contain a common
starting point "A1" and different stop values based upon
the number of rows in a specific opened file?
 
J

Jake Marx

Hi jmp,
how do I define a variable range that can contain a common
starting point "A1" and different stop values based upon
the number of rows in a specific opened file?
Click to expand...

There are several ways to do this. Here are a few:

Sub test()
Dim nCols As Integer
Dim lRows As Long
Dim rng As Range

nCols = 10
lRows = 5

'/ method 1
Set rng = Sheets("Sheet1").Range("A1").Resize(lRows, nCols)

Debug.Print rng.Address

'/ method 2
With Sheets("Sheet1")
Set rng = .Range(.Cells(1, 1), .Cells(lRows, nCols))
End With

Debug.Print rng.Address

Set rng = Nothing
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
B

BOHICA

jmp said:
how do I define a variable range that can contain a common
starting point "A1" and different stop values based upon
the number of rows in a specific opened file?
Click to expand...

If it needs to be every value from A1 to a certain cell, you have a
few choices.

1. If the cell that it stops will never be deleted, and you add rows
between them, you can name the cell and then go from A1:namedcell

2. If you just need it to select the rows that have data values in
them and just that (and there are no spaces) you could use the
following bit of code:

Dim xNum
Dim Fooxx As String


Range("A1").Select

Charck:
If Not ActiveCell.value = "" Then
xNum = xNum + 1
ActiveCell.Offset(1, 0).Activate
GoTo Charck
End If


Then, you can use this as the number of rows offset from A1. The
variable xNum would equal the number of times it sees cells with data
in them. Once it completes this, then your range select could look
like this:

Fooxx = "A1:A" & xNum

Range(Fooxx).Select


I think that will work. If it is off by one row, then just before the
"Fooxx = "A1:A & xNum" line, add a line that states: xNum = xNum + 1

Also, for a larger data range to include more columns, you can mess
with the string Fooxx line (For example, if you need range A1 to D
whatever, you can change the line to: Fooxx = "A1:D & xNum
 
Top