A few words of thanks would be greatly appreciated.

Excel - A macro for deleting row on condition

Latest update on July 3, 2012 at 06:23 AM by aakai1056 .

Macros can be used for various operations on Excel Worksheets such as deleting rows that satisfy a particular given condition. Although not necessary, some knowledge of programming concepts and for loops may prove to be useful to write a macro in Excel. Macros in Excel to delete rows that satisfy certain conditions can be written from scratch or developed from a freely available macro code. Macros are a very useful feature and can be used to accomplish data manipulations on Excel Worksheets with a large number of records. Macros in Excel automate the process making the task fast and error-free.

Issue
Solution
Note

Issue

I need a Macro for deleting rows when none of the cells in the row contain one of two numbers "103526" or "103527"

The number of rows is variable (max 150) and number of columns variable too (max 100).

Any help is appreciated.

Solution

Your condition is confusing to me. I am rephrasing it.
If a row contains either 103526 or 102527 or both then DO NOT delete.
If neither of the two numbers is in a row, delete.
Now, as you are deleting the rows, keep a copy of the data in sheet1 to sheet2 so that you can copy the data if there is a mess up.

The data is in sheets 1, try this macro:

Sub test()    
Dim j As Integer, k As Integer    
Dim r As Range, cfind6 As Range, cfind7 As Range    
Worksheets("sheet1").Activate    
On Error Resume Next    
j = Cells(Rows.Count, "A").End(xlUp).Row    
For k = j To 1 Step -1    
Set cfind6 = Rows(k).Cells.Find(what:=103526, lookat:=xlWhole)    

Set cfind7 = Rows(k).Cells.Find(what:=103527, lookat:=xlWhole)    
If cfind6 Is Nothing And cfind7 Is Nothing Then Rows(k).Delete    

Next

Note

Thanks to venkat1926 for this tip on the forum.

Delete row macro
Excel - A macro to delete alternate rows - How-To - Office Software
Macros to Delete Rows in Excel 2007 ✓ - Forum - Programming
Macro to delete row after certain condition found ✓ - Forum - Excel
Excel Macro for deleting rows IF cells not... ✓ - Forum - Excel
Excel - One macro for multiple row tasks - How-To - Excel

This document, titled « Excel - A macro for deleting row on condition », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).

Excel - How to count rows which show specified dates?

Excel - IF is error with 2 cells match

Issue

Solution

Note

Subscribe To Our Newsletter!