Missing the gaps?

dan2

Board Regular
Joined
Mar 26, 2002
Messages
60
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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.
 
Upvote 0
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
:)
 
Upvote 0
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
:)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :eek:(

Anyone?

Dan.
 
Upvote 0
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"
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top