Delete Rows Based On Criteria Excel VBA Macro

Introduction

In this tutorial, we will learn how to delete rows in Excel based on specific criteria using VBA macros. This process is particularly useful for cleaning large datasets efficiently and can be adapted to various conditions such as numerical values or text strings. Whether you're managing inventory, sales data, or any other dataset, mastering this VBA technique will streamline your workflow.

Step 1: Open the Visual Basic Editor

1. Open your Excel workbook containing the data.
2. Press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, right-click on any of the objects for your workbook.
4. Select Insert and then click on Module to create a new module. This is where we will write our macro.

Step 2: Declare Variables

1. At the top of the new module, declare the necessary variables:

   Dim lastRow As Long
   Dim dataRow As Long
   Dim prodVol As Integer
   Dim prodPrice As Integer
   

   • lastRow will hold the number of the last row with data.
   • dataRow will be used to loop through each row.
   • prodVol and prodPrice will store the values for volume and price, respectively.

Step 3: Determine the Last Row

1. Use the following code to find the last row with data in the active sheet:

   lastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
   

   • This code uses column D (where product volume is located) to identify the last row.

Step 4: Loop Through Rows

1. Create a loop to go through each row from the last row to row 2 (to skip headers):

   For dataRow = lastRow To 2 Step -1
   

2. Inside the loop, get the values for volume and price:

   prodVol = ActiveSheet.Cells(dataRow, "D").Value
   prodPrice = ActiveSheet.Cells(dataRow, "F").Value
   

Step 5: Set Criteria for Deletion

1. Use an If statement to check if the criteria are met:

   If prodVol < 700 Or prodPrice >= 1000 Then
       ActiveSheet.Rows(dataRow).Delete
   End If
   

   • This checks if the volume is less than 700 milliliters or the price is 1000 or more. You can modify these conditions based on your dataset.

Step 6: Finalize the Macro

1. Close the loop and the subroutine:

   Next dataRow
   End Sub
   

2. Your complete macro should look like this:

   Sub DeleteRowsBasedOnCriteria()
       Dim lastRow As Long
       Dim dataRow As Long
       Dim prodVol As Integer
       Dim prodPrice As Integer
      
       lastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
       
       For dataRow = lastRow To 2 Step -1
           prodVol = ActiveSheet.Cells(dataRow, "D").Value
           prodPrice = ActiveSheet.Cells(dataRow, "F").Value
           
           If prodVol < 700 Or prodPrice >= 1000 Then
               ActiveSheet.Rows(dataRow).Delete
           End If
       Next dataRow
   End Sub
   

Conclusion

You've now created a VBA macro that efficiently deletes rows in Excel based on specified criteria. This powerful tool can be customized to meet different conditions and can save you considerable time when working with large datasets. As next steps, consider experimenting with different criteria or expanding the macro to include additional conditions to further automate your data management tasks.