Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 19th 06, 02:04 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2006
Posts: 39
Default Auto-detect range size

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.


  #2   Report Post  
Old May 19th 06, 02:20 AM posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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.


  #3   Report Post  
Old May 19th 06, 02:32 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2006
Posts: 39
Default Auto-detect range size

I am totally confused by what you typed but it works like a charm.
Thanks-a-million.

Shelton

  #4   Report Post  
Old May 19th 06, 04:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2006
Posts: 39
Default Auto-detect range size

I'm trying to copy this for use in column A as well but with no luck. I
think it must be that "2" at the end. What does it represent?

  #5   Report Post  
Old May 19th 06, 10:21 PM posted to microsoft.public.excel.programming
JMB JMB is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,062
Default Auto-detect range size

I will try to explain. First, anything inside the With/End With that is
preceded by a period refers back to the object in the first line of the With
statement - "Sheet2" in this case.

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

Range/Cell references inside of other range references also must refer to
the proper worksheet, or Excel will think you mean the active sheet (like the
reference to .Range("D2") above

One way a range can be defined by the beginning and ending cell. You know
the beginning cell is B2. We find the second cell by defining a row and
column reference.

..Cells(row,column)

the row is found by starting in D2 and jumping to the last cell that is
contiguous (similar to you selecting cell D2, holding down the Control key
and hitting the down arrow). This statement refers to that cell and we add
..Row on the end to get just the row reference of that cell.

..Range("D2").End(xlDown).Row

The 2 in the Cells reference refers to column B, as you suspected. So, you
could use

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

You could also save the Row reference of the last cell in column D to a
variable if you have to use it many times.

LastRow = WorkSheets("Sheet2").Range("D2").End(xlDown).Row



"shelfish" wrote:

I'm trying to copy this for use in column A as well but with no luck. I
think it must be that "2" at the end. What does it represent?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Detect strings in a range Rich57 Excel Worksheet Functions 4 January 1st 08 12:00 AM
Date Auto-detect & format Issues uw805 Excel Discussion (Misc queries) 2 April 17th 07 07:42 AM
Auto size a pivot table to a page size *.* Excel Worksheet Functions 1 August 23rd 06 05:09 AM
determine range size after auto filter? Jeff Excel Programming 3 December 30th 04 10:34 PM
how do i set up to auto detect duplicated numbers in an excel fiel Peabody Excel Discussion (Misc queries) 1 December 15th 04 08:01 AM


All times are GMT +1. The time now is 10:28 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017