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

In this article, I’ll show you how can develop a Macro to clean data from a data set using VBA in Excel.

Download Practice workbook

Download this practice workbook to exercise while you are reading this article.

4 Simple Methods to Develop a Macro to Clean up Data in Excel

Here we’ve got a data set in the range B2:B12 of a worksheet called Sheet1 that contains the Student IDs, Student Names, Marks, and Grades of some school students.

Our objective today is to develop a Macro to clean data from this data set.

1. Macro to Clean Data from a Single Column in Excel

First, we’ll develop a Macro to clean all the data from a single column. For example, let’s create a Macro to clean all data from the column titled Marks (Column 3 of the data set).

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’ll clean all the data from the 3rd column (Marks) of the given data set.

Read More: How to Clean Survey Data in Excel (with Detailed Steps)

2. Macro to Clean Data from Multiple Columns in Excel

Next, we’ll develop a Macro to clear data from multiple columns of the data set. For example, let’s clear all the data from the 1st and 3rd columns of the data set (Student ID and Marks).

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 (10 Examples)

3. Macro to Clean Data with a Single Criterion in Excel

Now, we’ll clean the data with a single criterion from this data set.

For example, let’s 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.

4. Macro to Clean Data with Multiple Criteria in Excel

Finally, we’ll learn to clean data with multiple criteria.

For example, let’s 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 4th example, we’ve used 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.

Conclusion

So, these are the ways to develop a Macro to clean data from a data set using VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Related Articles

• How to Clean Up Raw Data in Excel (10 Suitable Ways)
• How to Do Automated Data Cleaning in Excel (10 Easy Tips)