Working with non-specific cell references

[Guest]

I am trying to write a macro that will format a spreadsheet (containing data
downloaded from a Borland database) and perform quartile analysis on several
data blocks in the spreadsheet. The problem is that size of the data blocks
varies depending on the original query that was ran on the database, i.e. I
can't use specific cell references in the macro.

The dowloaded data always conforms to a genaral pattern so I've had some
success with formatting the worskeet using xlLastCell, xldown, etc to move
around the data. Unfortunately I've hit a brick wall with trying to include
quartile functions in the macro as they require specific cell references for
both the range of data to analyse and the cell in which the formula result
will be displayed.

I would be very apprreciative if anyone has any ideas that might help me.

Yours hopefully,

Gareth Packham
Analyst
PriceWaterhouseCoopers

 

[Peter T]

Hi Gareth,

I assume your formula can work with a Name, and your data is in it's own
block with headers.

Dim rng As Range

Set rng = Range("A1").CurrentRegion ' top left header cell

if rng.rows.count > 1 then

Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) ' exclude headers

ActiveWorkbook.Names.Add "Test", rng

else
???
end if

' =COUNTA(Test)

Regards,
Peter T