ComboBox query - why lots of blank rows?

[manc]

Good morning,

Workbook A Sheet 1 contains four columns
A = concatenate B & C
B = first name
C = cast name
D = phone number

The CONCATENATE formula in column A replicates down to line 50, as i can't be sure how many contacts there will be, but i do know that there won't be more than 50.

Workbook B Sheet 'Contacts' contains a query to fetch the data from Workbook A Sheet 1 on startup and displays the data in the same format as the source worksheet.

Workbook B Sheet 'Main' then contains a ComboBox that fetches data from column A from Workbook B 'Contacts' sheet.

However, the ComboBox has lots of blank rows.
How do i get rid of these?

The ComboBox is populating from a data validation drop-down list in A1 - courtesy of Contextures code that enables a ComboBox to appear when you double-click on a cell that contains a data validation drop-down list.

The data validation list source is '=CONTACTS'
Within Name Manger, CONTACTS is defined as a dynamic range =OFFSET(CONTACTS!$A$1,0,0,COUNTA(CONTACTS!$A:$A),1)
(However, when you select the range in name manager, the range is showing down to row 50 - should it not highlight the whole column? I can't see where i am referencing it only down to row 50??)

I've tried deleting the CONCATENATE formula in original source workbook from the rows that do not contain any data, just to see, but it made no difference.

Any ideas much appreciated. Whilst not the end of the world and is workable, it is just one of these frustrating issues that i am sure is easy to sort if you know how.

Best regards
manc

 

[steve the fish]

It because COUNTA will count your cells that have formula even if they appear blank. You said that deleting the formulas in column A hasnt solved the problem. That is strange because it should. Is there anything else in this column? If you used this what does it do:

=OFFSET(contacts!$A$1,0,0,SUMPRODUCT(--(LEN(contacts!$A$1:$A$50)>0)),1)

 

[manc]

Steve, thanks for your reply.

There is nothing else in the column.

If i change the source for the data validation drop-down list to
=OFFSET(contacts!$A$1,0,0,SUMPRODUCT(--(LEN(contacts!$A$1:$A$50)>0)),1)
neither the drop-down list or the combobox populate.

Best regards
manc

 

[steve the fish]

What are you getting if selecting that range in name manager?

 

[manc]

Apologies,

Using
=OFFSET(contacts!$A$1,0,0,SUMPRODUCT(--(LEN(contacts!$A$1:$A$50)>0)),1)the list is populating but still the blank rows.

In name manager, the range selected is A1:A48

Best regards
manc

 

[steve the fish]

Ok do you have spaces in B or C? All that is doing is counting cells that have length greater than 0. If they appear blank but have a length >0 then it must be spaces.

 

[manc]

No, column's B & C contain both first names and last names on all populated rows. No blank cells.
Rows 1-10, all columnB cells contain a name, all columnC cells contain a name, all columnD cells contain a number.
ColumnA is CONCATENATEing B&C on just rows 1-10. Formula has been deleted from columnA row 11 onwards for the purpose of sorting this issue. Row 11 onwards is blank for all columns - no nothing entered!

Best regards
manc

 

[manc]

Steve,

I've tried it with a new sheet, pasting the data from sheet'CONTACTS' just as a value, and then linking the combobox to the new sheet, using =OFFSET(contacts!$A$1,0,0,SUMPRODUCT(--(LEN(contacts!$A$1:$A$50)>0)),1) and there are no blank rows.
Maybe it is something to do with the query. I wonder whether to use VBA to import the data rather than the query function?
I'm going to have a play and see what i can find out.

Best regards
manc

 

[steve the fish]

The names are all in order? No blank rows inbetween the names? Tell me what this equals:

=SUMPRODUCT(--(LEN(contacts!$A$1:$A$50)>0))

Tell me the concatenate formula you are using. Finally if it still doesnt work copy cells B1:C50 and paste them here.

 

[steve the fish]

The concatenate formula could be it. Are you putting a space in there to seperate the names? If so you could use:

=SUMPRODUCT(--(contacts!$A$1:$A$50<>" "))