Formula to quickly select a blank range of x number of cells

[asking]

Hi all,
Using excel 2007 in Vista

In column G, I have 500 cells with names
In cell H11 I have the formula =UniqueItems(G11:G511)
The UniqueItems is a function macro provided by ~
http://spreadsheetpage.com/index.php/tip/C32

The answer in the formula is 387. I now wish to transpose those Unique items into a range using an array formula ~
=TRANSPOSE(UniqueItems(G11:G511,FALSE))

My question is how can I select the number of blank cells indicated by the answer of the first formula to enter the array starting at cell I11 without dragging down all the time.

I need to do the formulas for many different sheets so my next sheet the formula answer may be 280 to be entered into cell J11 and down to J291 etc...

Any advice would be welcome.

Cheers, Skinman.

 

[Sektor]

In VBA this will look like this. Change "Cells" to your range.

Sub SelectBlanks()
    Cells.SpecialCells(xlCellTypeBlanks).Select
End Sub

 

[Aladin Akyurek]

H11, copy down:

=IFERROR(INDEX(UniqueItems($G$11:$G$511,FALSE),ROWS($H$11:H11)),"")

This eliminates the need for calling TRANSPOSE and selecting an area as big as G11:G551 and applying control+shift+enter.

 

[asking]

Thanks for the replies.

Aladin Akyurek your answer works (very slow compared to the array formula) but you still have to fill (drag down) for 387 cells which is what I wanted to eliminate.

However by entering either the array formula or your formula into H11 and shift clicking on the bottom of the cell takes you down the required amount of blank cells. Then for your formula, Control D, or in array formula control+shift+enter.

Sektor thanks for the reply but for what you suggested, naming the range would be just as effective. Doing this many times for different sheets would mean either changing the macro range continually or changing a named range continually.

What I originally thought was that I could use the answer in the formula =UniqueItems(G11:G511) to quickly select that amount of blank cells in column 'I, starting from cell I11.

Thanks again for the prompt replies.
Skinman

 

[Aladin Akyurek]

Thanks for the replies.

Aladin Akyurek your answer works (very slow compared to the array formula) but you still have to fill (drag down) for 387 cells which is what I wanted to eliminate...

I have no idea what you mean by slow... INDEX with ROWS is not a combination that one would call slow.

A formula needs either to be (a) copied down or (b) its results generated at once.

The site you quoted wants you to apply the latter. In order to do that:

a) you need to enter the following formula in H11:

=TRANSPOSE(UniqueItems($G$11:$G$511,FALSE))

b) then select an area as large as the source data. In this case: H11:H511.

c) Apply control+shift+enter.


Or:

H10:

=UniqueItems($G$11:$G$511)

H11:

=TRANSPOSE(UniqueItems($G$11:$G$511,FALSE))

Select the number of rows H10 specifies from H11 on.

Apply control+shift+enter.

Or:

Solllicite for code that does all of this for you.

 

[asking]

Aladin Akyurek

It must be my computer, I just timed your Index formula after filling down, for the answers to appear it took 22 seconds, the array formula was instant. I have a an intel 2 extreme cpu x7900 @2.8GHz 32 bits.

Thanks again.

 

[Aladin Akyurek]

Aladin Akyurek

It must be my computer, I just timed your Index formula after filling down, for the answers to appear it took 22 seconds, the array formula was instant. I have a an intel 2 extreme cpu x7900 @2.8GHz 32 bits.

Thanks again.

Would you please time also the following set up:

H10:

=UniqueItems($G$11:$G$511)

H11, just enter and copy down:

=IF(ROWS($H$11:H11)<=$H$10,INDEX(UniqueItems($G$11:$G$511,FALSE),ROWS($H$11:H11)),"")

 

[asking]

Thanks for taking the time ~

I did exactly as you said and this time 23 seconds. Initially, it instantly places the first name in all cells, then 23 seconds later all the correct names appear.

Cheers

 

[Aladin Akyurek]

Thanks for taking the time ~

I did exactly as you said and this time 23 seconds. Initially, it instantly places the first name in all cells, then 23 seconds later all the correct names appear.

Cheers

OK. Thanks for carrying out the timing.