• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA script to insert new rows based on data in other cells... then cut data from those cells and paste into those new rows

Status
Not open for further replies.

mbnnyc

New Member
Hi, I have a spreadsheet that contains mailing list data. It's in a format in which there are Alt1Email (column R), Alt2Email (column S), Alt3Email (column T), and Alt4Email (column U). Wanna cut and paste that data from those columns to the Email column (column Q) in NEW ROWs based on how many AltEmails the contact has. Then ultimately delete columns R,S,T, and U. I thought this out in a plain logic script (below) but being new to VBA I'm having trouble understand syntax a bit. Please if anyone can assist would greatly appreciate. Kind regards! Attached is the spreadsheet and screenshots. Using MS Excel 2013 for Windows and VBA 7.1 that comes with it. Thanks!

----------

{If cells in columns R,S,T,U are all Populated (not null) in the row the VBA script is working on... first row being row TWO... so R2,S2,T2,U2)

then

INSERT FOUR new rows BELOW this row

AND

COPY from that "reference" row the cell values from columns A thru P... to the corresponding columns of the 4 new rows just created,

AND

CUT cell value from column R AND PASTE IT to the FIRST new row immediately under the original "reference" row into COLUMN Q

AND

CUT column S value and paste to SECOND new row's Q column

AND

CUT column T value and paste to THIRD new row's Q column

AND

CUT column U value and paste to FOURTH new row's Q column}

OR

{If out of R,S,T,U.... only columns R,S, & T (only 3 alt-emails) are populated in the current "reference" row

then

INSERT THREE new rows below this row

AND

COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,

AND

CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row

INTO COLUMN Q

AND

CUT column S value and paste to SECOND new row's Q column

AND

CUT column T value and paste to THIRD new row's Q column}

OR

{If out of R,S,T,U, only R & S (only 2 alt-emails) are populated in that "reference" row

then

INSERT TWO new rows below the reference row

AND

copy from that "reference" row the cell values from columns A thru P... and PASTE to the corresponding columns of the THREE new rows just created,

AND

CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row to COLUMN Q

AND

CUT column S value and Paste to SECOND new row's Q column}

OR

{If out of R,S,T, U, only column R is populated in that particular row)

then

INSERT ONE new row below the reference row

AND COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,




AND

CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row into COLUMN Q}

{If we just added 4 new rows, move down FIVE rows from the original row... if this new “reference” row is a totally blank row, END this script,

If not, make THIS row the new “reference” row AND go back to beginning of script

if we just added 3 new rows, move down FOUR rows from the original row... if this is a totally blank row, END this script

If not, make THIS row the new “reference” row AND go back to beginning of script

{if we just added 2 new rows, move down THREE rows from the original row... if this is a totally blank row, END this script,

If not, make THIS row the new “reference” row AND go back to beginning of script}

{if we just added 1 new row, move down 2 rows from the original row... if this is a totally black row, END this script, if not, make THIS row the new criteria row}

{if we added no new rows in the above process, move down to the next row below original row. if this is a totally BLANK NULL row, END this script,}

Go back to beginning criteria of this script
 

Attachments

  • Add-Specific-Number-of-Rows-And-Populate-Based-On-Cell-Value.xlsx
    12.2 KB · Views: 3
  • BEFORE-new-row-inserts-and-data-copys-and-cuts.png
    BEFORE-new-row-inserts-and-data-copys-and-cuts.png
    44.8 KB · Views: 2
  • AFTER-new-row-inserts-and-data-copys-and-cuts.png
    AFTER-new-row-inserts-and-data-copys-and-cuts.png
    62 KB · Views: 4
Last edited:
As wild cross posting you must read the forum rules below and do the necessary (what any forum expects for) :​
 
Posted in other threads as well:






 
Status
Not open for further replies.
Back
Top