Delete Rows Based On Criteria Excel VBA Macro

In this post we see how to delete rows based on given criteria in Excel using VBA macros. The macro loops through each row with content in the worksheet, and checks if certain criteria for one or more fields is met to delete the row. The criteria can be any value along with the relational operators of the If statement. For example, that could be when the value is below a certain price, or when the profit is above a certain margin. It can also check for specific text, a string of certain length, etc. In the below example, we delete rows for products with a volume below 700 ml or price above 1000 (of the given currency) in the dataset.

Macro/VBA code:

 

  Sub DeleteRowsOnCriteria()

 

      Dim lastRow As Long, dataRow As Long

      Dim prodVolume As Integer, prodPrice As Integer

 

      lastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

 

      For dataRow = lastRow To 2 Step -1

   

          prodVolume = Range("D" & dataRow).Value

          prodPrice = Range("F" & dataRow).Value

 

          If prodVolume < 700 Or prodPrice > 1000 Then

              Rows(dataRow).Delete

          End If

 

      Next dataRow

 

  End Sub

 

 

Macro explained:

• We declare two variables to store the number of the last row with content, and the row number in the dataset while looping. These could be declared as Integer, but that would prompt an error if the dataset has more than 32767 rows. To avoid that, we declare it as Long, which can store numbers in the range of millions too. We also declare two Integer values to store the volume and price while looping through the dataset.
• Then we get the last row with content in the active sheet – that should be the sheet with the target dataset. There are several ways to get the last row with content. In this case, is probably a good idea to look for the used range in that worksheet (we assume that’s the dataset range), and get the row of the last cell with content in that dataset. Note that, if the dataset is not homogeneous (i.e. it does not have the same number of rows for each column or field), this could give erroneous results.
• Then we start a loop through each row, from the last row with content up to row 1 or 2 (use row 2 if there are headers), therefore using Step -1.
• Now we get the values of the field or fields of interest as per the selected criteria. In the example above, we get the volume of product (the liquid of the drink or beverage, in ml), and the price of the product (a bottle with a given volume of liquid).
• Then we have the main condition with the given criteria. In this example, we check for products with a volume below 700 ml or price above 1000 (of the given currency). If that condition is met, we delete the row.

• We can set any other criteria, either numerical or also checking for text. And we can concatenate as many conditions as needed with the logical operators (And, Or, Not).

 

This is how we delete rows based on criteria with Excel VBA macros.

 

Other examples:

• How to Upload Data To Google Sheets with Excel VBA macros
  
• How to Consolidate Filtered Data From Multiple Sheets with Excel VBA macros
  
• How to Combine All Workbooks In Folder with Excel VBA macros