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!
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!
Last edited by kisboros; 04-06-2013 at 11:39 AM. Reason: Want to attach spreadsheet
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.
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsmPlease Login or Register to view this content.
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.
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.Please Login or Register to view this content.
What shall I modify?
Thanks!
Last edited by kisboros; 04-05-2013 at 05:57 PM.
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
Sorry about that; I have corrected it.
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
kisboros,
The macro worked correctly with the data in your attached workbook.
I will have to see the actual raw data in worksheet Alaska - Electricity. Please attach the workbook with this worksheet.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?
Last edited by stanleydgromjr; 04-05-2013 at 06:52 PM.
Hi Stan,
I couldn't upload all 6000 rows so I took a snippet from the original. Hope it will suffice.
Thank you,
Kisboros
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
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.
Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsmPlease Login or Register to view this content.
Then run the Remove23215RowsV2 macro.
Thanks, Stan.
Works well!
kisboros,
You are very welcome. Glad I could help.
Thanks for the feedback.
Come back anytime.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks