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.

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


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

VBA Code to Develop Macro to Clean Up Data in Excel

⧭ 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

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

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.

Output to Develop a Macro to Clean up Data in Excel

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

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

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

2 Comments
  1. 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!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo