PDA

View Full Version : Dynamic range to account for blank cells



Sir Babydum GBE
01-23-2008, 03:58 AM
Hi,

I have a sheet where the headers are in row 5 Columns A to L, data starts in row 6, the problem is any column could contain a few blank cells.

How do I do a dynamic range that starts in A5, and takes me to column L - to the last row with any data in it please?

Thanks

BD

Bob Phillips
01-23-2008, 04:13 AM
Dim rng As Range

With ActiveSheet

Set rng = .Range("A5").Resize(, .Cells(5, .Columns.Count).End(xlToLeft).Column)
End With

Sir Babydum GBE
01-23-2008, 04:16 AM
Thanks XLD

Ah, how would I refer to that range in a non-vba formula?

BD

Bob Phillips
01-23-2008, 05:15 AM
This creates a range in that row

OFFSET(A5,,,1,MAX((COLUMN(INDIRECT("5:5")))*(5:5<>"")))

Sir Babydum GBE
01-23-2008, 05:57 AM
Thanks Mr X

Don't know where I'd be without you. (Well, I'd probably still be in Cardiff - but that's not really what I meant)

BD