Working with huge data is always exciting and challenging. From the 2007 version onward, Excel is supporting more than a million rows in each worksheet.
One of the common problems with huge data is “Duplicates” and the bigger problem is to identify and remove these duplicates.
In this article, we will be sharing 4 ways to delete duplicate records from your data.
In the first method, let’s make use of Excel’s inbuilt functionality of removing duplicates.
Note: If your data has extra spaces at the end of the value (for example ‘Friedrick’ and ‘Friedrick’ then Excel may not be able to detect those duplicates. In this method, we will use VBA code to call the Remove Duplicates function and delete the duplicate records
Following is the VBA Syntax and sample VBA macro command to delete duplicate rows from the worksheet using VBA. We are using the Remove Duplicates method of the Cells object of the worksheet.
Cells.RemoveDuplicates Columns:=Array(
[Column Numbers])
Here Cells.Remove Duplicates command tells excel to remove the duplicated based on the specified list of column array. Columns:=Array([Column Numbers]) will help us to specify the list of columns to combine and treat it as a duplicate record.
Sub sbRemoveDuplicates() Cells.RemoveDuplicates Columns:=Array(1) End Sub
'VBA code to remove duplicates from data with headers Sub sbRemoveDuplicatesSpecificWithHeaders() Range("A1:D10").RemoveDuplicates Columns:=Array(1), Header:= xlYes End Sub
‘Starting procedure to write VBA code to remove duplicates from data with no headers Sub sbRemoveDuplicatesSpecificWithNoHeaders() Range("A1:D10").RemoveDuplicates Columns:=Array(1), Header:= xlNo End Sub
COUNTBLANK function is used to get the total count of Blank or Empty cell in range.
COUNTBLANK Function has one required argument i.e. range.
SUBSTITUTE function is used to substitute the existing old text to new text.
VBA Code To Delete All Shapes On A Excel sheet Here is a VBA code which deletes all the shapes from an Excel sheet. Code is simple but you have to be bit careful while…
In this post, you’ll learn how to Show formulas in Excel spreadsheets. Usually, when you create a formula in Excel, it shows the result in the cell. But sometimes, you might want to see the…
Time Management is very effective way of managing the available time. Current competitive world is making the time management crucial. There are various ways, tools and techniques by which time management can be done easily.
Excel has built-in options to make graphs, it doesn’t have a heat map feature. However, you can quickly and easily create a heat map in Excel using conditional formatting.