Macro for Unique Values to be shown on separate tab

JaimeDee

New Member
Joined
May 13, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Please can anyone help. I am super new to VBA so apologies if this is too basic a question. I want to set up a macro button on my tool bar that copys a column that I highlight into another tab and then removes duplicates as well as removing any blanks to leave me with unique values. When I run the below, it copys the header but not the data within the whole column. This means after it has done its cleanse, I am just left with the header of the column on its own on a separate sheet? What am I doing wrong?

I intend to store this in my personal macro workbook so I can run it on any document. Not sure if that makes a difference at all?!
VBA Code:
Dim rSelection As Range
Dim ws As Worksheet

 'Check that a range is selected
  If TypeName(Selection) <> "Range" Then
    MsgBox "Please select a range first.", vbOKOnly, "List Unique Values Macro"
    Exit Sub
  End If
 
  'Store the selected range
  Set rSelection = Selection

  'Add a new worksheet
  Set ws = Worksheets.Add
 
  'Copy/paste selection to the new sheet
  rSelection.Copy
 
  With ws.Range("A1")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
    '.PasteSpecial xlPasteValuesAndNumberFormats
  End With
 
  'Remove duplicates
  ws.UsedRange.RemoveDuplicates Columns:=1, Header:=xlGuess
 
  'Remove blank cells (optional)
  On Error Resume Next
    ws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
  On Error GoTo 0
 
  'Autofit column
  ws.Columns("A").AutoFit
 
  'Exit CutCopyMode
  Application.CutCopyMode = False
  
End Sub
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,381
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What did you select before you ran the macro? All the data or just the first cell in the column?
 

JaimeDee

New Member
Joined
May 13, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
What did you select before you ran the macro? All the data or just the first cell in the column?
Hi, Thank you for such a quick reply. This is my first post on the forum.

I selected the column letter (my data isn't in a formal excel "table" its just data in cells if you know what I mean. So I just clicked on the column label (which was column F) so the whole column was highlighted
 

Forum statistics

Threads
1,141,863
Messages
5,709,074
Members
421,613
Latest member
wyzco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top