How to Use Macro to Clean Up Data in Excel (4 Methods)

Below is a dataset in the range B2:B12 of a worksheet called Sheet1 that contains some school students’ Student IDs, Student Names, Marks, and Grades.

Method 1 – Using Macro to Clean Data from a Single Column in Excel

The VBA code will be:

⧭ VBA Code:

Sub Clean_Data_from_Single_Column()

Sheet_Name = "Sheet1"
Data_Set = "B2:E12"
Delete_Column = 3

Set Data_Range = Worksheets(Sheet_Name).Range(Data_Set)

Data_Range.Cells(1, Delete_Column).EntireColumn.Delete

End Sub

⧭ Output:

• Run the code after changing the inputs. It will clean all the data from the third column (Marks) of the given dataset.

Read More: How to Clean Survey Data in Excel

Method 2 – Using Macro to Clean Data from Multiple Columns in Excel

We’ll take the column numbers into an array this time.

The VBA code will be:

⧭ VBA Code:

Sub Clean_Data_from_Multiple_Columns()

Sheet_Name = "Sheet1"
Data_Set = "B2:E12"
Delete_Columns = Array(1, 3)

Set Data_Range = Worksheets(Sheet_Name).Range(Data_Set)

For i = UBound(Delete_Columns) To LBound(Delete_Columns) Step -1
    Data_Range.Cells(1, Delete_Columns(i)).EntireColumn.Delete
Next i

End Sub

⧭ Output:

• Run the code (after changing the inputs). It’ll clear all the data from the given columns in the data set (Columns 1 and 3 here).

Read More: Using Excel to Clean and Prepare Data for Analysis

Method 3 – Using Macro to Clean Data with a Single Criterion in Excel

• Delete all the rows where the Mark (column 3) is less than 40.

The VBA code will be:

⧭ VBA Code:

Sub Clean_Data_with_a_Single_Criteria()

Sheet_Name = "Sheet1"
Data_Set = "B2:E12"
Criteria_Column = 3

Set Data_Range = Worksheets(Sheet_Name).Range(Data_Set)

For i = Data_Range.Rows.Count To 2 Step -1
    If Data_Range.Cells(i, Criteria_Column) < 40 Then
        Data_Range.Cells(i, Criteria_Column).EntireRow.Delete
    End If
Next i

End Sub

⧭ Output:

• Run this code. It’ll delete all the rows in the data set where the mark is less than 40.

Read More: How to Remove Partial Data from Multiple Cells in Excel

Method 4 – Using Macro to Clean Data with Multiple Criteria in Excel

• Delete all the rows where the Student ID (Column 1) is less than 150, or the Mark (Column 3) is less than 40.

The VBA code will be:

⧭ VBA Code:

Sub Clean_Data_with_Multiple_Criteria()

Sheet_Name = "Sheet1"
Data_Set = "B2:E12"

Set Data_Range = Worksheets(Sheet_Name).Range(Data_Set)

For i = Data_Range.Rows.Count To 2 Step -1
    If Data_Range.Cells(i, 1) < 150 Or Data_Range.Cells(i, 3) < 40 Then
        Data_Range.Cells(i, Criteria_Column).EntireRow.Delete
    End If
Next i

End Sub

⧭ Output:

• Run this code. It’ll delete all the rows in the data set where the student ID is less than 150, or the mark is less than 40.

Read More: 19 Practical Data Cleaning Techniques in Excel

Things to Remember

In the fourth example, we’ve combined two OR-type criteria to make a combined criterion. If you have two AND-type criteria instead, use And in the code instead of Or.

Download the Practice workbook

Download this workbook to practice.

Related Articles

• How to Clean Up Raw Data in Excel
• How to Do Automated Data Cleaning in Excel

<< Go Back To Data Cleaning in Excel | Learn Excel