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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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 the 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.