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/