Here's an interesting one...

[Andrew Slentz]

This one really has me stumped. I have a spreadsheet (see below) which
contains (at random times) a group with members in it. The beginning of
the group, and all mailing address/contact info., is identified with a
leading row which has "Group" in column B. All subsequent members of
that particular group will have "Group Member" in the B column and will
not have an address or any contact info. I need to get one macro which
will take the contact info. (column c,d,e,f) and copy it to all group
members. I then need another to delete all rows with "group" in the B
column and finally the B colum in its entirety. Any ideas??? I also
need to know how to change the comuln designations in case the layout
changes. Remember, there are multiple groups in a spreadsheet but each
member of a group immediately follows the group heading! In the
scenario below the address, city, state (and colum G&H) would be copied
from row 2 to rows 3,4,5,6. Rows 1 and 7 never get touched. Another
macro then deletes all rows like Row 2 and then deletes the B column.

Anyone who figures this out will definitely be my hero!

Thanks,

Andrew

ORIGINAL:
A B C D E F
1)[Name1] [data1][Address1][City1] [State1]
2)[Name2] "Group" [Address2][City2] [State2]
3)[Name3] "Group member" [data2]
4)[Name4] "Group member" [data3]
5)[Name5] "Group member" [data4]
6)[Name6] "Group member" [data5]
7)[Name7] [data6][Address3][City3] [State3]

Needed (after macro):
A B C D E F
1)[Name1] [data1][Address1][City1] [State1]
2)[Name2] "Group" [Address2][City2] [State2]
3)[Name3] "Group member" [data2][Address2][City2] [State2]
4)[Name4] "Group member" [data3][Address2][City2] [State2]
5)[Name5] "Group member" [data4][Address2][City2] [State2]
6)[Name6] "Group member" [data5][Address2][City2] [State2]
7)[Name7] [data6][Address3][City3] [State3]


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Ken Wright]

See if this does what you want:-

Select C1:F1000 (or whatever last cell is)
Do Edit / Go To / Special / Blank cells
Type = and hit the UP arrow once, then hit CTRL+ENTER
You should now have filled in all the blanks as necessary, and if the data looks
good thenselect C1:F1000 again, do edit / Copy then Edit / Paste Special /
Values.

Now Select all of Col B and do Data / Filter / Autofilter and filter on "Group"
Do Edit / Go To / Special / Visible cells only, then do Edit / Delete / Entire
Row

Now select Col B and delete it

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

This one really has me stumped. I have a spreadsheet (see below) which
contains (at random times) a group with members in it. The beginning of
the group, and all mailing address/contact info., is identified with a
leading row which has "Group" in column B. All subsequent members of
that particular group will have "Group Member" in the B column and will
not have an address or any contact info. I need to get one macro which
will take the contact info. (column c,d,e,f) and copy it to all group
members. I then need another to delete all rows with "group" in the B
column and finally the B colum in its entirety. Any ideas??? I also
need to know how to change the comuln designations in case the layout
changes. Remember, there are multiple groups in a spreadsheet but each
member of a group immediately follows the group heading! In the
scenario below the address, city, state (and colum G&H) would be copied
from row 2 to rows 3,4,5,6. Rows 1 and 7 never get touched. Another
macro then deletes all rows like Row 2 and then deletes the B column.

Anyone who figures this out will definitely be my hero!

Thanks,

Andrew

ORIGINAL:
A B C D E F
1)[Name1] [data1][Address1][City1] [State1]
2)[Name2] "Group" [Address2][City2] [State2]
3)[Name3] "Group member" [data2]
4)[Name4] "Group member" [data3]
5)[Name5] "Group member" [data4]
6)[Name6] "Group member" [data5]
7)[Name7] [data6][Address3][City3] [State3]

Needed (after macro):
A B C D E F
1)[Name1] [data1][Address1][City1] [State1]
2)[Name2] "Group" [Address2][City2] [State2]
3)[Name3] "Group member" [data2][Address2][City2] [State2]
4)[Name4] "Group member" [data3][Address2][City2] [State2]
5)[Name5] "Group member" [data4][Address2][City2] [State2]
6)[Name6] "Group member" [data5][Address2][City2] [State2]
7)[Name7] [data6][Address3][City3] [State3]


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!