Copying the column heading into all non-empty cells

[LizzyD]

Gord you're a genius!

Thanks so much - that works a treat! Sorry I didn't get back to yo
last night - I'm in London and it was past my bedtime.

The reason I'm copying the headings into the rows by the way, is that
have a database which I want users to be able to search, and it's easie
if I just have have one column listing all the Amenities for each hotel
rather than 120.

Incidentally, my next move is to now merge all the cells in each ro
into one, with the amenities separated by spaces. I've just downloade
a "Merge Cell Wizard", which has accomplished this, but there's n
option to ignore blank cells, which makes my final list of amenitie
somewhat stretched out by unnecessary spaces.

It's not really a big deal but if anyone does know a way t
delete/ignore blank cells within a row I'd be very interested to hea
it. Otherwise, no worries - I've only been searching for half an hou
so far - I'm sure I'll find a way pretty soon!

Thanks very much again for you help Gord - I really appreciate it!

Lizzy

 

[Gord Dibben]

Lizzy

Keep on truckin'. Here is a User Defined Function that will ignore blank
cells.

Insert a column left of Column A or anywhere you like, because you will be
deleting the original data after.

Usage is: =ConCatRange(A1P1)

or whatever your last column is. I assumed DP because it is column 120.

Drag/copy down as far as you have data.


Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) > 0 Then sbuf = sbuf & Cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function


Again, copy and paste special>values then delete original data.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord