Finding a Dynamic Range

[walkest]

Hello,
I have an ever changing range I would like to get a handle to in excel VBA. It always starts at H2, but can end anywhere e.g. I4 or AQ156. Does anyone know how I get a handle to the range from ("H2: [possibly anywhere]&quot ?
Stacey

 

[xlbo]

Try this
[H2].select
Selection.CurrentRegion.Select
then
with selection
.....
end with
HTH
Geoff

 

[Loomah]

Walkest
Another idea is to use the Used Range. However, Excel's UsedRange property is unreliable. But if you have a look at this thread - thread707-255327 - all the building blocks are there to dynamically select the full range of used cells.

That said, if your data is contiguous, go with xlbo's suggestion!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[SkipVought]

I like xlbo's suggstion with one exception.

Unless there is some reason to actually select a range, I believe that it is usually better to reference the ranges directly...

With [H2].CurrentRegion
    'now here is where you can manipulate the properties and methods of this range reference like...
   'find the first row/column, last row/column
   FirstRow = .Row
   FirstCol = .Column
   LastRow = FirstRow + .Rows.Count - 1
   LastCol = FIrstCol + .Columns.Count - 1
...
End With

Here's where NOT selecting can really speed things up running your code. Does not matter what sheet you are on, If you modify the With statement to...

With Worksheets(SheetName).[H2].CurrentRegion...

and furthermore, if you were to define a Range Object for H2 as follows...

Dim rngH2 as Range

Set rngH2 =  Worksheets(SheetName).[H2]

With rngH2.CurrentRegion
...

can reference that range without regard to what sheet is active.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

As usual Skip is correct - didn't want to go into all of that without knowing what needed to be done with the range. Have to admit that I don't usually optimise my code, mainly because I rarely work on anything big enough to need it - must work on that one ;-)
G

 

[SkipVought]

Geoff,

You are ALWAYS right at the top with good solutions.

As you indicated, I often deal with ALOT of data. In the aircraft manufacturing industry, I often deal with 10's of thousands of rows and dozens of columns. So I probably do spend more time on code optimization. And...

somtimes I get carried awaaaaaaaaaaay!!! Skip,
SkipAndMary1017@mindspring.com