Please note javascript is required for full website functionality.
MVP

Blog

VBA Blog: Deleting Specific Rows

13 September 2019

Welcome back to the VBA blog.  This week, we are going to learn how to use VBA to delete specific rows.

Today we are going to create VBA script to delete rows with even numbers in an example ID field automatically.  It is a useful method to conduct data ETL (extracting, transforming and loading) for the specific format of a data table.

Essentially, we would like to delete the records with even number in the ID field:

The result would look like this:

The first step is to define relevant variables. We defined two variables lngRow and i

Dim lngRow As Long

Dim i As Long

Then, we locate the last row of data in column A and assign the value to variable “lngRow”.

lngRow = Range("A" & Rows.Count).End(xlUp).Row

We then use an ‘If’ statement to determine the value used for next loop.  

This requires ‘Mod’ which determines the remainder when a value is divided by a divisor.  For example, the ‘Mod’ of the value five (5) using the divisor three (3) is the remainder two (2) – that is. 5 / 3 = 1 r 2. Therefore, if the ‘Mod’ value of lngRow with divisor two (2) equals to 0 (i.e. it is an even number), then the value of lngRow is further reduced by one (1).

If lngRow Mod 2 = 0 Then

   lngRow = lngRow – 1

End If

After this step, we use the ‘for loop’ to start from the row determined by variable “lngRow” and loop through backward to row 2 with step -2.  This would help to locate the row with even number of ID as shown in the data table and then delete the entire row.

For i = lngRow To 2 Step -2

        Rows(i).Delete

Next i

Combing all the lines of code together, we get this:

Sub DeleteRows()

 

    Dim lngRow As Long

    Dim i As Long

   

    lngRow = Range("A" & Rows.Count).End(xlUp).Row

    If lngRow Mod 2 = 0 Then

        lngRow = lngRow - 1

    End If

   

    For i = lngRow To 2 Step -2

        Rows(i).Delete

    Next i

 

End Sub

By using this method, we can delete the specific row based on rules without manual and repetitive operation.

See you next week for more VBA tips!

Newsletter