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.
Hello sir i see your website about excel work its realy great and help me alot to see different codes.Can u please tell me it is possible i connect excel to jama software with vba and write a command that the files in jama software can automatically export in excel when i run the macro that connect vba to jama open
Hello, AMNA SHAHBAZ!
This is Sabrina, one of the authors of Exceldemy. First of all, thank you for your comment. Actually, we don’t work with jama software. So, we are not sure whether it’s possible or not!