Remove Excel Duplicates Any Column Instantly (Code Included)

Here's my free to use VBA code that will instantly output the unique items of any active cell's column into a new worksheet in a new workbook without altering your original worksheet data.

As usual I've added comments to help you understand each section of the VBA code. Refer back to the video to further help your understanding and the method to create the Macro short cut key so you can quickly launch the VBA Macro from your keyboard.

Enjoy!
---------------------------------------------------------------------------------------------------------------------
Option Explicit
Sub Show_Unique_Column_Values()

'***************************************************************************************************
'******* Developed by ExcelPowerTips - support my Youtube Channel - Subscribe - Like - Share ****
'***************************************************************************************************

'Step 1: Declare variables after Inserting a Standard Module (Right Click: INSERT -- MODULE)
Dim lngLastRow As Long 'used to calculate the last data row of the active column
Dim intColumn As Integer 'used to get the column number of the active cell

'Step 2: Get column number of active cell column
intColumn = ActiveCell.Column

'Step 3: Get genuine last row of active cell column by working backwards from maximum rows in Excel worksheet
lngLastRow = ActiveSheet.Cells(Application.Rows.Count, intColumn).End(xlUp).Row

'Step 4: Copy the exact dimensions of the active column onto the clipboard memory
ActiveSheet.Range(Cells(1, intColumn), Cells(lngLastRow, intColumn)).Copy

'Step 5: Add a new workbook and paste clipboard contents - this will always be pasted in column A of the new worksheet of new workbook
Workbooks.Add
ActiveSheet.Paste

'Step 6: Remove duplicates in place of the copied data into column A of the new workbook
ActiveSheet.Range("A1:A" & lngLastRow).RemoveDuplicates Columns:=1, Header:=xlYes

'Step 7: Disable cut copy mode
Application.CutCopyMode = False

'Step 8: Find the number of unique values after removing duplicates excluding header row in the new workbook sheet
lngLastRow = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row - 1

'Step 9: Show volume of unique values listed in column A in cell B1
ActiveSheet.Range("B1").Value = " Unique values: " & lngLastRow

'Step 10: Autofit column A,B width and select cell A1
ActiveSheet.Columns("A:B").AutoFit
ActiveSheet.Range("A1").Select

'Step 11: After testing code next Create a macro shortcut key by selecting on
'Excel Menu: Macros -- Options --- Choose your choice of shortcut Key so you can run the macro code from the keyboard

'The Module single VBA Macro saved also saves the shortcut key so you don't have to create it again when using the VBA macro in a new workbook

'Enjoy!

End Sub

Видео Remove Excel Duplicates Any Column Instantly (Code Included) канала ExcelPowerTips