+ Reply to Thread
Results 1 to 12 of 12

Remove unwanted rows in Excel 2010

  1. #1
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    128

    Remove unwanted rows in Excel 2010

    Dear Gurus,

    How can I mass-remove rows where the GL string contains 23215? I have about 6000 rows. Example attached
    Thank you very much!
    Attached Files Attached Files
    Last edited by kisboros; 04-06-2013 at 11:39 AM. Reason: Want to attach spreadsheet

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Remove unwanted rows in Excel 2010

    kisboros,

    Thanks for the workbook.

    With your raw data in worksheet Sheet1, beginning in cell A1, per your attached workbook.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Please Login or Register  to view this content.
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    hen run the Remove23215Rows macro.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Remove unwanted rows in Excel 2010

    Please Login or Register  to view this content.
    Stanley: I have replaced the "sheet 1" with the actual sheet name, but I get an error message: Run-time error '9': Subscript out of range.
    What shall I modify?

    Thanks!
    Last edited by kisboros; 04-05-2013 at 05:57 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,910

    Re: Remove unwanted rows in Excel 2010

    kisboros...

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Remove unwanted rows in Excel 2010

    Sorry about that; I have corrected it.

  6. #6
    Registered User
    Join Date
    04-05-2013
    Location
    dublin, Ireland
    MS-Off Ver
    Excel 2007/10
    Posts
    26

    Re: Remove unwanted rows in Excel 2010

    Another option (NON VBA) is to insert a column next to the data. If you insert the formula "=+IF(ISERROR(FIND("23215", A2)), 1, 1/0)" into E2 this will generate a 1 if it cannot find 23215 - otherwise it will generate a #DIV/0! error. (1/0 cannot be done)

    This will generate an error in the lines with 23215.

    Highlight the entire column - You can then hit CTRL + G which will bring up the Goto window - click Special - then click Formulas - the uncheck each box except Errors and hit OK

    This will highlight all the error cells in the column. Then click the home tab (if in 2007/2010 - dunno about 2003) & over on the right 'Delete > Delete Sheet Rows'

    Bingo Bango Bongo

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Remove unwanted rows in Excel 2010

    kisboros,

    The macro worked correctly with the data in your attached workbook.

    Stanley: I have replaced the "sheet 1" with the actual sheet name, but I get an error message: Run-time error '9': Subscript out of range.
    What shall I modify?
    I will have to see the actual raw data in worksheet Alaska - Electricity. Please attach the workbook with this worksheet.
    Last edited by stanleydgromjr; 04-05-2013 at 06:52 PM.

  8. #8
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Remove unwanted rows in Excel 2010

    Hi Stan,
    I couldn't upload all 6000 rows so I took a snippet from the original. Hope it will suffice.
    Thank you,
    Kisboros

  9. #9
    Forum Contributor Portuga's Avatar
    Join Date
    02-20-2004
    Location
    Portugal
    MS-Off Ver
    365
    Posts
    852

    Re: Remove unwanted rows in Excel 2010

    You can always use the auto filter "contains" option which is pretty fast.

    In VB:

    Please Login or Register  to view this content.
    If you found the solution to your question. Mark the thread as "Solved"
    Thank everyone that helped you with a valid solution by clicking on their

    There is no such thing as a problem, only a temporary lack of a solution

  10. #10
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Remove unwanted rows in Excel 2010

    kisboros,

    Thanks for the correct workbook/worksheet.

    The below updated macro works on your new data set.

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Please Login or Register  to view this content.
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the Remove23215RowsV2 macro.

  11. #11
    Forum Contributor
    Join Date
    01-18-2010
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    128

    Re: Remove unwanted rows in Excel 2010

    Thanks, Stan.
    Works well!

  12. #12
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Remove unwanted rows in Excel 2010

    kisboros,

    You are very welcome. Glad I could help.

    Thanks for the feedback.

    Come back anytime.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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