I have a list of SSNs (social security numbers) along with various line
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 ther
are multiple lines for each SSN. This is quite an extensive list and
need the appropriate SSN to extend to each line that is associated wit
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 entir
thing.
Any help would be greatly appreciated!!
ti

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

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/

I think this method will work, I can sort out the SSN lines I don't nee
after this. Thanks

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

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/

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/

it works fine Stephen. There is no copying and pasting of the formula
required beyond the steps I described.

Entering a relative reference will definitely cause it to be adjusted in the
other cells when Ctrl+Enter is used with multiple cells selected.

A: I had no discussion of visible cells. I said blank cells
B: I didn't say Ctrl+Shift+Enter, this isn't an array formula. I said
Ctrl+Enter
C: I use this successfully everyday.

I never suggested this method to be used with an autofilter, nor did the
OP's original post allude to the fact that working with an autofilter might
be required.

as to your problem, the table probably at some point, originally had

=if(condition,"",formula)

when this is copied and pasted special, it replaces the formula with a null
string. this has a length of zero, but the cell is still not considered
blank.

--
Regards,
Tom Ogilvy



"Stephen Rasey" wrote in message
...
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/

Re Ctrl-Enter instead of Ctrl-Shift-Enter
My mistake. I use Ctrl-Shift-Enter so much I did it out of habit.
Thanks, Tom

Stephen Rasey
Houston