View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Auto-detect range size

First example is the equivalent to going to cell D2, holding the Control key
and pressing the down arrow, the second is equivalent to going to D65536,
holding the Control key and pressing the up arrow. Which one you use depends
on if there are gaps in your data in column D.


Sub test1()
Dim rngTest As Range

With Worksheets("Sheet2")
Set rngTest = .Range("B2", _
.Cells(.Range("D2").End(xlDown).Row, 2))
End With

MsgBox rngTest.Address
End Sub


Sub test2()
Dim rngTest As Range

With Worksheets("Sheet2")
Set rngTest = .Range("B2", _
.Cells(.Cells(.Rows.Count, _
4).End(xlUp).Row, 2))
End With

MsgBox rngTest.Address
End Sub



"shelfish" wrote:

I'm fairly new to VBA. I'm taking db output and wanting to work with
it. However, the number of rows varies greatly. The only way I can
address one of the columns as a range is to use an input box to ask the
user to select the cells. Put another way, I'd like to use the logic
used by autofill to know where to stop to know where to establish the
end of the range. Is there an existing way to do this that I haven't
found?

Written generally, here are the are the steps to my script:

1. Insert columns in A and B.
2. In B2, Contatenate C2, D2
3. Autofill Destination:=[here's my problem, range unknown]

OR I could do it this way

1. Insert columns in A and B
2. Do While [same problem, establish size of range in D]
3. Contatenate as above
4. Offset to next row
5. Loop

There is probably a much improved way to do either of these but like I
said, I've only been at this for about a week. Thanks in advance for
any assistance.