# 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!