Missing the gaps?

dan2 · Mar 29th, 2002, 03:38 PM

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.

Chris Davison · Mar 29th, 2002, 03:47 PM

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 ]

dan2 · Mar 29th, 2002, 04:30 PM

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.

Chris Davison · Mar 29th, 2002, 04:39 PM

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

Chris Davison · Mar 29th, 2002, 04:46 PM

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

dan2 · Mar 29th, 2002, 05:07 PM

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.

Dave Hawley · Mar 29th, 2002, 05:43 PM

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.

dan2 · Mar 29th, 2002, 05:48 PM

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.

Dave Hawley · Mar 29th, 2002, 05:54 PM

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"

daleyman · Mar 29th, 2002, 06:35 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 03:38 PM	#1
dan2 Board Regular Join Date: Mar 2002 Posts: 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.

Mar 29th, 2002, 03:47 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ]

Mar 29th, 2002, 04:30 PM	#3
dan2 Board Regular Join Date: Mar 2002 Posts: 60	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.

Mar 29th, 2002, 04:39 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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

Mar 29th, 2002, 04:46 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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

Mar 29th, 2002, 05:07 PM	#6
dan2 Board Regular Join Date: Mar 2002 Posts: 60	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.

Mar 29th, 2002, 05:43 PM	#7
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 29th, 2002, 05:48 PM	#8
dan2 Board Regular Join Date: Mar 2002 Posts: 60	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.

Mar 29th, 2002, 05:54 PM	#9
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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"

Mar 29th, 2002, 06:35 PM	#10
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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