+ Reply to Thread
Results 1 to 4 of 4

Inserting, Cutting, and Pasting Rows Based on What's in a Cell

  1. #1
    Registered User
    Join Date
    08-01-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Inserting, Cutting, and Pasting Rows Based on What's in a Cell

    Summary of what I want to do:
    I have been working on this part of my macro where I want it to add some rows with text in the rows based on a letter that is in a cell of that row. I also want a separate part of the script to cut and paste a row based on the letter that is found in a particular cell of that row. lastly, I want to check if a column has something in it or not and delete columns that don't have anything in it...

    First Script:
    If there is an "N" in Column B, then I want the macro to insert 2 blank rows above that "N" if there is no "N" already in the row above it. Also type in bold font "Row inserted" in column A of the first inserted blank row (The one above the column with the "N" in it).

    For an example, if Row3 Column B had the letter "N" in it, then the result would be the words typed "Row Inserted" in Row1 Column A in bold font with Row2 being completely blanks and then Row3 Column B having the letter "N" in it.

    Second Script:
    In a separate part of the script script. If there is an "O" in Column B, then I want to cut the entire row which contains the clumn with the "O" in it and paste it below the last row that has an "N" in column B but this time I want to skip 2 rows so there are two blanks rows bafter the row with the "N" in the B column and before the row with the "O" in the B column. then type in bold font "Row Inserted" in Column A of the row that was inserted and is right above the column that has the "O" in it. The result would be the column with the O cut and pasted two rows below the last column with the "N" in it and the words typed 'Row Inserted" in bold above it in Column A

    Third Script:
    I want the script to check if there are empty cells in column A, and if there are then delete the rows with the empty cells.... not just deleting the cells, but the entire rows so that the rows come together and column A does not have any gaps in it between cells that have values in them.


    Visual examples of what I am describing in Script 1 and 2 are attached.

    Any help is appreciated. Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Inserting, Cutting, and Pasting Rows Based on What's in a Cell

    Hi there!

    I have added a spreadsheet here for your reference.

    I have assumed that the data of your format as given in the data sheet. And the actual operations are done in Raw Data sheet. There are two macros, 1 is Format_N and another one is Format_O. Please check and let me know if you were looking for same.

    Thanks,
    Vikas
    Attached Files Attached Files
    Last edited by vikas.bhandari; 08-05-2010 at 03:57 AM. Reason: Added the template

  3. #3
    Registered User
    Join Date
    08-01-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Inserting, Cutting, and Pasting Rows Based on What's in a Cell

    That is exactly it! thanks!!! you rock!

  4. #4
    Registered User
    Join Date
    08-01-2010
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Inserting, Cutting, and Pasting Rows Based on What's in a Cell

    This scrip works great.. I have been testing it and I found one more thing though. I noticed that I delete all the N's and leave only the L's the formula wants to go to the top and of the page and add "Row Inserted" at the top of the page minus 2 cells... is there a way to make the formula stop of there are no N's and to just insert 1 row above the top "O" and put "Row inserted"? this way it separates itself from the L's if there are no N's rather than going haywire.

    Also, in both the N script and the O script.. when it inserts the new rows that do not have the text "Row Inserted" in them, can it clear those dived rows? so that if anything in column C,D,E,F... etc that happens to copy down would be cleared out and eliminated?

    Great scrip!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1