Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Missing the gaps?

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

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HIghlight the range, hit F5 (goto), then special, then constants..... this will highlight your non-blank cells

    click in the range to copy and paste to your new destination

    (my original thought was copy / paste special / skip blanks, but this doesn't seem to work..... can any one explain what the skip blanks does ?)

    hope this helps
    Chris





    edit..... ignore my question, I see what it does now.... grrrrr !

    [ This Message was edited by: Chris Davison on 2002-03-29 14:51 ]

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

    Default

    That would if I was to do it manually but the sheet the list is going to is hidden and the end user cant see it, so it needs to happen automatically with formulas on the hidden sheet.

    Sorry for not explaining it properly.

    Any ideas?

    Dan.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jeez Dan, what am I ? A mind reader ?!!!

    *chuckle*

    not sure you can do this with formulae or without unhiding the sheet to do stuff.... you may find the VBA experts will sort you out though, they're good !

    Sorry I couldn't fix your problem
    Chris

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    actually, couldn't you do what I suggested onto the existing visible sheet so you have your gap-less list

    ie starting at D100

    then just link the first cell in your hidden sheet to cell D100 on your original sheet and copy down.

    (ie =originalsheet!D100)

    or even =if(originalsheet!D100="","",+originalsheet!D100) which will not show a "0" in your list.

    not as pretty as some swift VBA code though..

    HTH
    Chris

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

    Default

    Thanks Chris

    I agree with you I think it needs some VBA love to work smoothly.

    Anyone know if this is possible in VBA?

    Thanks
    Dan.

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

    Default

    Hi dan2


    What about just running some code on the names to sort them ? This will force out all blanks and give the user an alphabetical list.



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

    Default

    I would love to but unfortunately the users need them to stay in the same order.ie.

    From To
    John John
    Mary Mary
    Paul
    Paul Fred

    Fred

    etc.

    I'm starting to get a sore head over this one (

    Anyone?

    Dan.

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

    Default

    Stick with it Dan, there is always a way

    With Sheet2
    .Columns(1).EntireRow.SpecialCells(xlBlanks).Delete
    .Range("A1", Range("A65536").End(xlUp)).Name = "MyRange"
    End With
    Combobox1.RowSource = "MyRange"




  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    alternatively, should you want a bit more control....




    Sub get_newlist()
    'configure these settings...

    source_sheetname = "sheet1"
    source_column_number = 1
    source_startrow = 1
    source_endrow = 1000

    destination_sheetname = "haha"
    destination_column_number = 1
    destination_startrow = 2


    'clear destination column, you might want to change this to clear
    'only the destination cells you want cleared, or add a line at the end
    'of the procedure to put a title on the new list

    Sheets(destination_sheetname).Cells(1, source_column_number).EntireColumn.ClearContents

    'move nonblank values across
    destinationrow = destination_startrow
    For rowx = source_startrow To source_endrow
    strg = Sheets(source_sheetname).Cells(rowx, source_column_number).Value
    If strg <> "" Then
    Sheets(destination_sheetname).Cells(destinationrow, destination_column_number).Value = strg
    destinationrow = destinationrow + 1
    End If
    Next

    End Sub



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
  •