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.

Data Set to Develop a Macro to Clean up Data in Excel

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

VBA Code to Develop Macro to Clean Up Data in Excel

⧭ Output:

Run the code after changing the inputs. It’ll clean all the data from the 3rd column (Marks) of the given data set.


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

VBA Code to Develop a Macro to Clean up Data in Excel

⧭ 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).

Output to Develop a Macro to Clean up Data in Excel


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.

Output to Develop a Macro to Clean up Data in Excel


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.


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.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo