Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Missing the gaps?

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  2. #12
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    It wont go above 200 names

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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

  4. #14
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi dan2


    Couldn't you use my second suggestion?

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin

    There can be one added if needed

    Dan.

  6. #16
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #17
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,035
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default

    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




  8. #18
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •