Returning A Range

**twills** · 07-19-2010, 07:47 AM

Hi,

I have a very simple query that I'm sure someone will be able to solve in seconds.

I have a spreadsheet and I'd like to get a specific range returned for use in a later function.

The range itself starts in cell A3 and extends downwards until the cell just above an empty cell in the same column (A). The trick of course is that the number of cells with data in them may change and hence the position of the empty cell in column A may also change.

I've attached a spreadsheet with a simple depiction of what I mean.

Should you have any further questions or queries, please don't hesistate to ask.

Many thanks in advance for your help,

Kindest Regards,
Twills.

**snb** · 07-19-2010, 07:53 AM

Explore 'currentregion' in the VBEditor helpfiles.

**sweep** · 07-19-2010, 07:56 AM

Something like this?

Set RangeNeeded = Range(Range("A3"), Range("A3").End(xlDown))

**romperstomper** · 07-19-2010, 07:55 AM

For example:

    Dim rng As Range
    If Len(Range("A4").Formula) = 0 Then
        Set rng = Range("A3")
    Else
        Set rng = Range("A3", Range("A3").End(xlDown))
    End If