Missing the gaps?

[dan2]

I'm probably missing something obvious here!

I have a column of names which has gaps occasionally in it (not in regular places) and I want it so that it copies to a blenk sheet as a column with no gaps so I can use it for a combobox.

Names will be added and deleted at various times and I want the solid list to stay upto date.

Thanks for any help in advance

Dan.

 

[Aladin Akyurek]

On 2002-03-29 14:38, dan2 wrote:
I'm probably missing something obvious here!

I have a column of names which has gaps occasionally in it (not in regular places) and I want it so that it copies to a blenk sheet as a column with no gaps so I can use it for a combobox.

Names will be added and deleted at various times and I want the solid list to stay upto date.

Thanks for any help in advance

Dan.

Dan,

What is the maximum number of names that you expect?

Aladin

 

[dan2]

Aladin

It wont go above 200 names

 

[Aladin Akyurek]

It wont go above 200 names

I secretely hoped you'd say 25. OK, since it seems you want a system of formulas that automatically constructs the name list, without disturbing their order of appearance, with gaps removed, which are due to additions to/deletions from the area they occupy.

One last question: Is there a column of numeric data type (dates or numbers) in the data area where the names are?

Aladin

 

[Dave Hawley]

Hi dan2


Couldn't you use my second suggestion?

 

[dan2]

Aladin

There can be one added if needed

Dan.

 

[dan2]

Dave

I know I am being thick here but I am fairly new to VBA.

The second idea you gave - 2 questions

1. where does the code go?
2. does it automatically update all the time?

Thanks for you patience

Dan.

 

[Aladin Akyurek]

There can be one added if needed

Dan,

I see you have been urged to stick with VBA. If that option is faster and fully automatic, you should go for it instead of what follows, a system of formulas.

I'll assume the following sample data in A1:A12 in a sheet called Data.

{"Names";
"xza";
"dan";
"gord";
"aladin";
"";
"tara";
"don";
"jack";
"";
"karl";
"asha"}

In a separate worksheet, named say BBoard (from blackboard),

in A2 enter and copy down as far as needed:

=IF(ROW()<=$B$1,IF(LEN(Data!A2),Data!A2,0),"")

In B1 enter:

=MATCH(REPT("z",40),Data!$A:$A)

In B2 enter:

="$A$2"&":"&ADDRESS(B1,1)

In B3 enter:

=ADDRESS(2,3)&":"&ADDRESS(B1,3)

In B4 enter:

=SUMPRODUCT((LEN(OFFSET(C2,0,0,B1,1))>0)+0)

In C2 array-enter and copy down as far as needed, that is, up to row 200:

=IF(ROW()-ROW(INDIRECT($B$3))+1>ROWS(INDIRECT($B$2))-COUNTIF(INDIRECT($B$2),0),"",INDIRECT(ADDRESS(SMALL((IF(INDIRECT($B$2)<>0,ROW(INDIRECT($B$2)),ROW()+ROWS(INDIRECT($B$2)))),ROW()-ROW(INDIRECT($B$3))+1),COLUMN(INDIRECT($B$2)))))

In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

Activate Insert|Name|Define.
Enter NList (from name list) as name in the Names in Workbook box.
Enter as formula in the Refers to box:

=OFFSET(BBoard!$C$2,0,0,BBoard!$B$4,1)

Now you can use NList as source in data validation or as input range in a ComboBox created via View|Toolbars|Forms.

Aladin

 

[Legacy 98055]

Hi
Hey Dan.
Why not just loop through the column and send it to where you wish?
You can tell I'm new to VBA. Loop this, Loop that. Loop here, Loop there. I love Looping.

Sub LoopIt_Babeeeeeee()
Dim RowCntr As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim PlacementRowCntr

'first row on sheet sending the data
FirstRow = 1 'or ?
'estimate last row or use a cute formula, I'm too lazy
LastRow = 500 'or ?
'first row on sheet recieving the data
PlacementRowCntr = 1 'or ?

For RowCntr = FirstRow To LastRow
If SheetFrom.Range("A" & RowCntr).Value <> "" Then
SheetTo.Range("A" & PlacementRow).Value = _
SheetFrom.Range("A" & RowCntr).Value
PlacementCntr = PlacementCntr + 1
End If
Next

End Sub

Put it in a button, event, or wherever.
And if this doesn't work, then Loopty Doooo!
I'm not as funny as I think!
Have a nice day!
Tom a.k.a The Loop King!