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
VBA to Read Excel Data Using Connection String Sometimes as a programmer you need to read heavy (more then 5 MB) Excel files. There are two ways you can read data from Excel files: Open…
In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder
To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.
Excel Function ISERROR Microsoft Excel “ISERROR Function” is a Logical Function and it is used to check if cell contains any “ERROR”. “ISERROR Function” is used as a test to validate if cell contains any…
SUBSTITUTE function is used to substitute the existing old text to new text.
File Properties Tool is an MS Excel based tool which helps you to get File Name, File Path, Date Created, Date Last Accessed, Date Last Modified, Size (MB) and File Type properties of the files. You just need to browse the folder where your files are and click on ‘Get File Properties’ button.