Scratch the last paragraph. The first paragraph is correct. (=A1,
Ctrl-Shift-Enter) will not work. You must
=A1 in the first cell, Copy, Select all visble blank cells, Paste. Then
you can copy the column, Copy, Paste Value.

Stephen Rasey
Houston

"Stephen Rasey" wrote in message
...
That's a good one, Tom.
But (=A1, Ctrl-Shift-Enter) will put =A1 into all the visible cells, not
the
relative reference.
You need to put =A1 into the A2 visible cell, Copy it, then paste into all
the other visble blank cells.

I tried it on a table that came from who-knows-where and I got a curious
result. Goto Special could not find any blank cells even though a blank
cell was obviously there. Autofilter, show (blank), showed that row that
Goto Special couldn't find. The =LEN() of that blank cell was zero.

When I selected the "empty" cell and put the cursor on the formula edit
bar,
press delete, there were no characters to delete. I press enter. Then
Goto Special could find the cell.

Evidently, in my list a blank cell could be "", a null string. Goto
Special will not find these.

Using your technique, using AutoFilter, choose (blank), then selecting
column, you should be able to apply the =A(1) (or =R[-1]C), then
Ctrl-Shift-Enter to fill in the visible cells.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org

"Tom Ogilvy" wrote in message
...
select the first column

do Edit=goto=special and select Blank Cells

then go to the formula bar and put in a formula like

=A1
this assumes the first blank cell, that active cell, is A2. Adjust so
the
formula refers to the cell above the active cell
Then do Ctrl + Enter
this enters the SSN in the blank cells.

Now select column 1 and do Edit=Copy , then Edit=Paste Special and
select
Values. This will replace the formulas with the hardcoded SSN.

do SSN's have leading zeros? if so, this might require some special
handling.

--
Regards,
Tom Ogilvy

"sixfivebeastman "
wrote
in
message ...
I have a list of SSNs (social security numbers) along with various
lines
of information that looks something like this:

123456789 , 1 , 64A , 114 , 75.11 , 1.7180
_________, 2 , 65A , 207 , 30.34 , 0.6050

Each of the commas represent a different cell.
In other words, the SSNs do not extend down for each line, and there
are multiple lines for each SSN. This is quite an extensive list and
I
need the appropriate SSN to extend to each line that is associated
with
it so that it would look like this:

123456789 , 1 , 64A , 114 , 75.11 , 1.7180
123456789 , 2 , 65A , 207 , 30.34 , 0.6050

How would I write a macro to do this?
I've tried using a loop but it refused to work so I scrapped the
entire
thing.
Any help would be greatly appreciated!!
tim


---
Message posted from http://www.ExcelForum.com/