referring to columns/cells using numbers

  • Thread starter Thread starter Masa Ito
  • Start date Start date
M

Masa Ito

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.
 
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
 
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)
 
Range generally supports string arguments or cell references ....
Range(Cells(3,4),Cells(3,232))
Click to expand...


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.
 
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
 
Tom Ogilvy said:
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
Click to expand...

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
 
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
 
Back
Top