referring to columns/cells using numbers
 

When I capture a cells location, I seem to only have the decimal
notation.

eg: (cycling through using a For each Col in range.columns
Worksheets("Data").Range("ColStart").Value = col.Column

So I store the column number (eg: 4), but I have a bit more difficulty
dealing with this #, rather than the letter notation (ie: F3). I use
this column later to define a range, but am having trouble:

eg:
rng = Worksheets(sSheetName).Range(3, 4)

This (3, 4) refers to row 3, column 4 - which I could use, but the syntax
for declaring the range seems more difficult. I have tried: (Range(3,
4:3, 232) but it doesn't like the colon.

Does anyone know how to define a range?

I know this should be a RTFM post - but I can't seem to search only the
VB part of the manual. I have a couple of Excel books, which have gotten
me to Range(3, 4), but not past that.

Thanks,

M.I.

referring to columns/cells using numbers
 

Hi

Try this

Sub test()
Dim rng As Range
With ActiveSheet
Set rng = .Range(.Cells(3, 4), .Cells(10, 6))
End With
rng.Select
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Masa Ito" wrote in message 48.16...
When I capture a cells location, I seem to only have the decimal
notation.

eg: (cycling through using a For each Col in range.columns
Worksheets("Data").Range("ColStart").Value = col.Column

So I store the column number (eg: 4), but I have a bit more difficulty
dealing with this #, rather than the letter notation (ie: F3). I use
this column later to define a range, but am having trouble:

eg:
rng = Worksheets(sSheetName).Range(3, 4)

This (3, 4) refers to row 3, column 4 - which I could use, but the syntax
for declaring the range seems more difficult. I have tried: (Range(3,
4:3, 232) but it doesn't like the colon.

Does anyone know how to define a range?

I know this should be a RTFM post - but I can't seem to search only the
VB part of the manual. I have a couple of Excel books, which have gotten
me to Range(3, 4), but not past that.

Thanks,

M.I.

referring to columns/cells using numbers
 

Range generally supports string arguments or cell references

Range("D3")

for you multicell range:

Range("D3:HX3")

Range("D3").Resize(1,229)

Range(Cells(3,4),Cells(3,232))

Range(Range("D3"),Range("HX3"))

Range("D3",Range("HX3"))

Range("D3","HX3")

Range(Cells(3,4)) is not legal. use Cells(3,4)


--
Regards,
Tom Ogilvy


"Masa Ito" wrote in message
48.16...
When I capture a cells location, I seem to only have the decimal
notation.

eg: (cycling through using a For each Col in range.columns
Worksheets("Data").Range("ColStart").Value = col.Column

So I store the column number (eg: 4), but I have a bit more difficulty
dealing with this #, rather than the letter notation (ie: F3). I use
this column later to define a range, but am having trouble:

eg:
rng = Worksheets(sSheetName).Range(3, 4)

This (3, 4) refers to row 3, column 4 - which I could use, but the syntax
for declaring the range seems more difficult. I have tried: (Range(3,
4:3, 232) but it doesn't like the colon.

Does anyone know how to define a range?

I know this should be a RTFM post - but I can't seem to search only the
VB part of the manual. I have a couple of Excel books, which have gotten
me to Range(3, 4), but not past that.

Thanks,

M.I.

referring to columns/cells using numbers
 

"Tom Ogilvy" wrote in news:exxpRrVqEHA.192
@tk2msftngp13.phx.gbl:

Range generally supports string arguments or cell references
....
Range(Cells(3,4),Cells(3,232))


Thanks Tom (and Ron) - very very helpful.

I am pulling the column value from a cell, but when I use this syntax:
Range(Cells(3,4),Cells(3,232))

it won't allow me to use the variable as the column. eg:
iColStart = Worksheets("Data").Range("ColStart").Value
For Each col In Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, 49)).Columns

I am used to coding in Visual Studio - not yet familiar with how to ctype
this so that it is in a format that is acceptable to this range
declaration. I know I am close, as I can make the code work by putting
in the number manually (as I did with the Cells(2, 49))

Really appreciate the help on this. Thanks to the help in here, and
plugging through, I think I am starting to get the hang of Excel VBA.

M.I.

referring to columns/cells using numbers
 

there is no reason that shouldn't work unliess iColStart does not hold a
number (or a string that can be converted to a number)

from the immediate window:
icolStart = 5
sSheetName = "Sheet1"
? Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, 49)).Columns.Address
$E$2:$AW$2

--
Regards,
Tom Ogilvy

"Masa Ito" wrote in message
48.16...
"Tom Ogilvy" wrote in news:exxpRrVqEHA.192
@tk2msftngp13.phx.gbl:

Range generally supports string arguments or cell references
...
Range(Cells(3,4),Cells(3,232))


Thanks Tom (and Ron) - very very helpful.

I am pulling the column value from a cell, but when I use this syntax:
Range(Cells(3,4),Cells(3,232))

it won't allow me to use the variable as the column. eg:
iColStart = Worksheets("Data").Range("ColStart").Value
For Each col In Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, 49)).Columns

I am used to coding in Visual Studio - not yet familiar with how to ctype
this so that it is in a format that is acceptable to this range
declaration. I know I am close, as I can make the code work by putting
in the number manually (as I did with the Cells(2, 49))

Really appreciate the help on this. Thanks to the help in here, and
plugging through, I think I am starting to get the hang of Excel VBA.

M.I.

referring to columns/cells using numbers
 

"Tom Ogilvy" wrote in

there is no reason that shouldn't work unliess iColStart does not hold
a number (or a string that can be converted to a number)

from the immediate window:
icolStart = 5
sSheetName = "Sheet1"
? Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, 49)).Columns.Address
$E$2:$AW$2


It must be my syntax. Apparently when referring to ranges in my for
loop, the following is acceptable:

Range("R2:BX2")

But this type is not:

Range(Cells(2, 18), Cells(2, 29))


I must be missing something simple with this notation...

I declare them like:

iColStart = 18
iColEnd = 29

Then do a loop like:

For Each col In Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, iColEnd)).Columns
col.Hidden = False
Next col

But it will not accept it. Although this type executes fine:

For Each col In Worksheets(sSheetName).Range("R2:BX2").Columns
col.Hidden = False
Next col


Masa

referring to columns/cells using numbers
 

You don't need a loop
I use Sheet1 (change to yours)
See the dots before cells, if you don't do that it will use the activesheet

Sub test()
Dim iColStart As Integer
Dim iColEnd As Integer
iColStart = 18
iColEnd = 29

With Worksheets("Sheet1")
.Range(.Cells(2, iColStart), _
.Cells(2, iColEnd)).EntireColumn.Hidden = False
End With

End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Masa Ito" wrote in message 48.16...
"Tom Ogilvy" wrote in

there is no reason that shouldn't work unliess iColStart does not hold
a number (or a string that can be converted to a number)

from the immediate window:
icolStart = 5
sSheetName = "Sheet1"
? Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, 49)).Columns.Address
$E$2:$AW$2


It must be my syntax. Apparently when referring to ranges in my for
loop, the following is acceptable:

Range("R2:BX2")

But this type is not:

Range(Cells(2, 18), Cells(2, 29))


I must be missing something simple with this notation...

I declare them like:

iColStart = 18
iColEnd = 29

Then do a loop like:

For Each col In Worksheets(sSheetName).Range(Cells(2, iColStart), _
Cells(2, iColEnd)).Columns
col.Hidden = False
Next col

But it will not accept it. Although this type executes fine:

For Each col In Worksheets(sSheetName).Range("R2:BX2").Columns
col.Hidden = False
Next col


Masa