Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Range Dynamically
Hello, I am trying to set Range dynamically with a variable and just can't
seem to figure out the proper method. Any help greatly appreciated. Plus, if someone knows a great tutorial on how to use Range, Cells, etc that would be appreciated also! Here is my code snippet which does not seem to properly define the range (Note: My var, colNum, is properly populating the column number field): Set rng = Range(Range(Cells(2, colNum)), Range(Cells(2, colNum)).End(xlDown)) TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Range Dynamically
On the up side you are really close... Try this...
Set rng = Range(Cells(2, colNum), Cells(2, colNum).End(xlDown)) -- HTH... Jim Thomlinson "DJS" wrote: Hello, I am trying to set Range dynamically with a variable and just can't seem to figure out the proper method. Any help greatly appreciated. Plus, if someone knows a great tutorial on how to use Range, Cells, etc that would be appreciated also! Here is my code snippet which does not seem to properly define the range (Note: My var, colNum, is properly populating the column number field): Set rng = Range(Range(Cells(2, colNum)), Range(Cells(2, colNum)).End(xlDown)) TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Range Dynamically
Colnum = 5
Set rng = Range(Cells(2, Colnum), Cells(2, Colnum).End(xlDown)) just put the "down" reference in the range function. -- Gary's Student "DJS" wrote: Hello, I am trying to set Range dynamically with a variable and just can't seem to figure out the proper method. Any help greatly appreciated. Plus, if someone knows a great tutorial on how to use Range, Cells, etc that would be appreciated also! Here is my code snippet which does not seem to properly define the range (Note: My var, colNum, is properly populating the column number field): Set rng = Range(Range(Cells(2, colNum)), Range(Cells(2, colNum)).End(xlDown)) TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Range Dynamically
Thanks Jim, I tried that (still have it commented out) but now it works like
a charm. BTW: Do you know how to echo out to the msg window the Range whch will be used? I tried the following but doesn't seem to work: MsgBox "The selected Range is " & rng.Text Thanks again. "Jim Thomlinson" wrote: On the up side you are really close... Try this... Set rng = Range(Cells(2, colNum), Cells(2, colNum).End(xlDown)) -- HTH... Jim Thomlinson "DJS" wrote: Hello, I am trying to set Range dynamically with a variable and just can't seem to figure out the proper method. Any help greatly appreciated. Plus, if someone knows a great tutorial on how to use Range, Cells, etc that would be appreciated also! Here is my code snippet which does not seem to properly define the range (Note: My var, colNum, is properly populating the column number field): Set rng = Range(Range(Cells(2, colNum)), Range(Cells(2, colNum)).End(xlDown)) TIA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Range Dynamically
MsgBox "The selected Range is " & rng.Address
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "DJS" wrote in message ... Thanks Jim, I tried that (still have it commented out) but now it works like a charm. BTW: Do you know how to echo out to the msg window the Range whch will be used? I tried the following but doesn't seem to work: MsgBox "The selected Range is " & rng.Text Thanks again. "Jim Thomlinson" wrote: On the up side you are really close... Try this... Set rng = Range(Cells(2, colNum), Cells(2, colNum).End(xlDown)) -- HTH... Jim Thomlinson "DJS" wrote: Hello, I am trying to set Range dynamically with a variable and just can't seem to figure out the proper method. Any help greatly appreciated. Plus, if someone knows a great tutorial on how to use Range, Cells, etc that would be appreciated also! Here is my code snippet which does not seem to properly define the range (Note: My var, colNum, is properly populating the column number field): Set rng = Range(Range(Cells(2, colNum)), Range(Cells(2, colNum)).End(xlDown)) TIA |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Setting Range Dynamically
Thank you!
"Bob Phillips" wrote: MsgBox "The selected Range is " & rng.Address -- --- HTH Bob (change the xxxx to gmail if mailing direct) "DJS" wrote in message ... Thanks Jim, I tried that (still have it commented out) but now it works like a charm. BTW: Do you know how to echo out to the msg window the Range whch will be used? I tried the following but doesn't seem to work: MsgBox "The selected Range is " & rng.Text Thanks again. "Jim Thomlinson" wrote: On the up side you are really close... Try this... Set rng = Range(Cells(2, colNum), Cells(2, colNum).End(xlDown)) -- HTH... Jim Thomlinson "DJS" wrote: Hello, I am trying to set Range dynamically with a variable and just can't seem to figure out the proper method. Any help greatly appreciated. Plus, if someone knows a great tutorial on how to use Range, Cells, etc that would be appreciated also! Here is my code snippet which does not seem to properly define the range (Note: My var, colNum, is properly populating the column number field): Set rng = Range(Range(Cells(2, colNum)), Range(Cells(2, colNum)).End(xlDown)) TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Ranges using non-contiguous cells and dependent on a cell value | Excel Worksheet Functions | |||
Setting a default sort for a range | New Users to Excel | |||
dynamically select a range of cells for use in a formula | Excel Worksheet Functions | |||
Setting source data range with Charts | Charts and Charting in Excel | |||
How can I dynamically eliminate blank cells in a given range in E. | Excel Worksheet Functions |