Deleting Unique Values

How do you delete unique values in a column?  I need to filter 7500+ rows to 
only display duplicate values. 
0
Utf
3/16/2010 9:31:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
2713 Views

Similar Articles

[PageSpeed] 9

The VBA code below will delete the entire row when the value in col. "A" will 
be uniqe.
Consider to make a copy of your entire sheet in order to test the code and 
see if this is what you need.
-------------------------------
Sub Delete_Uniques()
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For R = LR To 1 Step -1
           If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then
              Cells(R, 1).EntireRow.Delete
           End If
    Next
End Sub
---------------------
If you don't want to delete rows and instead only to delete the unique 
values - 
then change the command:
Cells(R, 1).EntireRow.Delete into: Cells(R, 1) = ""
Micky


"RK" wrote:

> How do you delete unique values in a column?  I need to filter 7500+ rows to 
> only display duplicate values. 
0
Utf
3/16/2010 10:07:01 PM
The code assumes the column is col. "A"
Micky


"RK" wrote:

> How do you delete unique values in a column?  I need to filter 7500+ rows to 
> only display duplicate values. 
0
Utf
3/16/2010 10:08:01 PM
Is there any way of tweaking this code so that the first instance of the 
duplicates are
removed as well? IE: Display 2 duplicate cells even though there are 3 
instances of the value

Thanks.

"מיכאל (מיקי) אבידן" wrote:

> The code assumes the column is col. "A"
> Micky
> 
> 
> "RK" wrote:
> 
> > How do you delete unique values in a column?  I need to filter 7500+ rows to 
> > only display duplicate values. 
0
Utf
3/17/2010 12:15:01 PM
Reply:

Similar Artilces:

Plotting 0 Values
I have a combination bar and line chart. Two series, Target and Actual are bars, the other, Forecast is a line. The following is an example of my data. 2005 Actual Jan Feb Mar Apr Target [INDENT] 37.0 37.0 37.0 37.0 Actual 38.5 39.3 36.4 35.8 39.3 Forecast [INDENT] 37.3 37.6 37.9 38.4 I have tried several ways to plot the chart. With and without the 200 Actual for Target and Forecast. I either get no 2005 actual data or th 2005 line includes points for Target and Forecast. It is okay...

Float max value
Hi Can anyone tell me why an attribute of type float is limited to 100,000,000,000? Is there any way to change this to a higher value? many thanks John Stevenson Hank, You can't change it through a supported way. If it's really needed, you could try to change directly the metadata on SQL Server. Although I do not know what could cause to the system. Regards, Leo Lopes @ L3 Many thanks Leo "Leo Lopes @ L3" wrote: > Hank, > > You can't change it through a supported way. If it's really needed, > you could try to change directly the metadata on SQL...

deny ability to delete sent items
Environment is Win2k3 Server and Exch2k3. We would like to impliment a Group Policy that would remove users ability to delete the items in their Sent Items folder. It would be OK if items could be removed after Archiving if necessary. It appears that deleted Sent Items do not go to Deleted Items folder, so dumpster retention policy doesn't seem to be a solution. Any ideas? I am 99.9% certain that this is not possible through group policy. I'm very certain that while it might be possible, it would be extremely difficult to implement and manage. -- Ed Crowley MVP - Exchange &quo...

Xml and unicode values with &#x...
Hello! I like to use the XmlTextWriter to write some SVG files. But in some cases, I need the '&' as '&' and not as &amp;. Example: <glyph unicode=3D"&#x4c;"/> Some code-snippet: XmlDocument ^ doc =3D gcnew XmlDocument(); .... XmlAttribute ^ a =3D doc->CreateAttribute("unicode"); a1->Value =3D ??? - what to write here? ... Stream ^ fs =3D gcnew FileStream(filename, FileMode::Create); ... XmlWriter^ writer =3D gcnew XmlTextWriter(fs, gcnew UTF8Encoding); = How can I force to write the att...

Can you delete and empty an open email
Hi there Can you delete and empty an open email? I know I can delete and empty a closed email by holding down shift and then deleting, but have not been able to figure out how to delete and empty from an open email. Is this even possible? Thanks for your help. Kylie On Wed, 18 May 2005 20:01:03 -1000, Kylie B"" <KylieB@discussions.microsoft.com> wrote: > Hi there > > Can you delete and empty an open email? I know I can delete and empty a > closed email by holding down shift and then deleting, but have not been > able > to figure out how to delet...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

junk e-mail file has been deleted
I have accidently deleted the junk e-mail folder, anybody know how to reset the folders? I have tried to restore it but it doesnt seem to work. Thanks bennettmi@aol.com <anonymous@discussions.microsoft.com> wrote: > I have accidently deleted the junk e-mail folder, anybody > know how to reset the folders? I have tried to restore it > but it doesnt seem to work. Assuming Outlook 2002 here. Start>Run. Enter "%ProgramFiles%\Microsoft Office\Office10\Outlook.exe" /ResetFolders (notice the quotes). Click OK. If Outlook 2002 is not your version, the "Office...

Email being sent to Deleted Items folder
Outlook 2002. Some emails I get (mainly mailing lists) get sent straight to my Deleted Items folder. I have NO rule set up to do this, and in fact I have added these mails to my exceptions list. They STILL get sent to my Deleted Items folder. How do I stop this? thanks. ...

How do I convert time (hh:mm) to value ($$) in Excel?
Would like to calculate cost of time. Eg. Cost for production down time per minute is $100. Says production doen for 3.5 hrs, what is formula shall I apply in order to generate the cost (in $$). =3.5*60*100 "ahfen79" wrote: > Would like to calculate cost of time. Eg. Cost for production down time per > minute is $100. Says production doen for 3.5 hrs, what is formula shall I > apply in order to generate the cost (in $$). =(3.5/24)*60*100 -- Regards Dave Hawley www.ozgrid.com "ahfen79" <ahfen79@discussions.microsoft.com> wrote in ...

How do I get deleted items that stay in inbox with line thru them.
I inherited a computer at work. Outlook is setup so that when I delete a message it stays in the inbox, with a line thru it. How do I get the deleted items to go automatically to the deleted item folder? "rwwhit" <rwwhit@discussions.microsoft.com> wrote in message news:7D17761E-CD1B-4D04-BACB-B0D837C4DD93@microsoft.com... >I inherited a computer at work. Outlook is setup so that when I delete a > message it stays in the inbox, with a line thru it. How do I get the deleted > items to go automatically to the deleted item folder? That's normal behavior...

Using a VBscript to copy/delete/move Outlook messages
I need to write a VBscript to: - open my Outlook (2000 or XP) mailbox - read the list of the folders in either the Exchange Mailbox and in local ..pst files - open each folder and subfolders - read information about messages (i.e. sender, recipient, creation date, and so on) - move, copy or delete messages based on the above information Can anybody please provide a pointer to a sample I can start from? Regards Marius ...

Excel 'hangs' when deleting a row
when i delete a row in excel 2000, everything locks up! and when lookup at Task Manager/Processes, it's MEM USAGE goes up to 22K o more! i tried doing it in another file but i experienced no error. the dat is in LAN. all data are filtered when the deletion began. however i ca delete files before. please help, anyone.... -- Message posted from http://www.ExcelForum.com The best thing is to free up memory (assuming your file is large). Clos all applications you do not require at the time and any workbooks no in use. Dunca -- Message posted from http://www.ExcelForum.com ...

HELP!! I accidentally deleted a folder I need. . .
I was cleaning up a long list of folders, and accidentally deleted a REALLY important one, that had a bunch in it. Can anyone tell me how I can recover them? They were extremely important ones!! Any help would be appreciated!! Thank you. Cleared your Deleted Items folder already? If not they will appear as a subfolder of Deleted Items -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Jennifer" <anonymous@discussions.microsoft.com> wrote in mes...

IE8 was deleted from my windows 7 and I cannot find correct downlo
I have a two month old Toshiba and deleted IE8 because of crashes. Trying to find the correct download to reinstall. The IE8 homepage just says it is included in windows 7 but does not show the download file....... ARRRG. Tried Vista IE8 download file but message says it not the correct operating system. On Sun, 21 Mar 2010 20:50:01 -0700, pawntonian <pawntonian@discussions.microsoft.com> wrote: >I have a two month old Toshiba and deleted IE8 because of crashes. Trying to >find the correct download to reinstall. The IE8 homepage just says it is >included in wind...

Validation Rule for field values
I have a database that includes a phone number field. I want to prevent a user from creating a new record that contains a phone number that is currently in a record that exists in the database.I am using a form for data entry into the table. Somehow I'd like for there to be a check to see if the phone number typed into the phone number field to be checked against existing phone numbers in the database.I've researched various sources but haven't been able to find the solution. I'm not familiar with VB so any replies that suggest using that will probably not work for me. Sorry, j...

Max of value from DataGroup2 within DataGroup1
Hello - I have a table with data as follows below. I am trying to build a query that will give me the record with MAX of specDiffMax [value] for each TestFreq [DataGroup 2] within each TestNum [DataGroup1]. ID TestNum TestFreq specDiffMax -------------------------------------------- 4889683 Test 1 1710 3.669998 5123289 Test 1 1710 2.882999 4817314 Test 1 1710 3.102001 5134007 Test 1 1710.2 3.573002 4896056 Test 1 1710.2 3.355 4914480 Test 1 1710.2 3.515999 4889685 Test 2 1710.4 3.333 4896057 Test 2 1710.4 3.450001 4914481 Test 2 ...

Emptying 'Deleted Items' folder on exit
I am running WLM version 2009 (Build 14.0.8089.0726) on Windows 7 with all the latest updates. I have 'Empty messages from the Deleted Items folder on exit' checked under Maintenance, but they're all still there when I exit and restart WLM. Is there another setting I should be looking at to make this work? TIA, -- SC Tom Tom, I experienced the same thing earlier today. I'm new to WLM and found that if you "checked" the ''Empty messages from the Deleted Items folder on > exit' after there were items in the Deleted Items folder, it ...

cannot delete
I have this message and cannot delete a bunch of spam emails that appeared all of a sudden from back in May when I set this all up. The are from other countries and are asking for personal info all 57 of them. The message windows mail says Could not select G mail on I map server. You might try refreshing your folder list to synchronize with I map server. Unknown Mailbox Gmail Failure. -- Patty Dallaire pattydallaire@gmail.com [Gmail] is a virtual folder which should not be polled for mail. In the left (folder) pane of Windows Mail, right-click on [Gmail], select Synchr...

Deleting Inventory Items
I've hit a snag trying to delete some unneeded inventory items. The items have no inventory, purchasing or sales activity of any kind. When trying to delete each I get a message that the item is a component of one or more bills of materials, but they are not. We do use the manufacturing modules but I've checked the BOM header and BOM line tables (BM010115 and BM010415) and these items don't appear anywhere. I've also checked the regular inventory bom tables just in case but they are not there either. Any thoughts on where else GP might be checking? -- Jim@TurboChef...

Deleting Empty Rows using VBA code without the need to activate sh
I utilize forms for running an excel spreadsheet. I have a macro which deletes empty rows (of a specified range). I have it so the macro works over several sheets. The macro works fine, however it needs to activate and perform its actions in each sheet in turn to select the rows and delete them. I now activate the macro from a userform, and was wondering if there was a way to execute the code such that the macro doesnt require each sheet to be activated (ie visible) to work. It is quite distracting having the sheets scrolling through in the background. I tried to activat...

How to Change Value of Active Control
I'm writing a routine to change the value of any Active Control to null. I can get the name of the control using ActiveControl.Name but I can't figure out the syntax to change the value of this control. I've tried assigning it to variables but variables aren't working in a: Forms![variable]![variable] = "" Help "Sondreli" <Sondreli@discussions.microsoft.com> wrote in message news:AA05A840-2AFE-4F98-ABE9-5D2DD17AE0B8@microsoft.com... > I'm writing a routine to change the value of any Active Control to null. > I > can g...

Allowing input, but restricting deletion
Hi all, Is it possible to allow input into a spreadsheet, but at the same time making sure that data that is inputted is impossible to delete? Hope you guys can help, Roland You may be able to do something with some sort of worksheet event. But I'd be worried about typos. How would you go about fixing those if the cell was locked after I finished my initial input? innovate wrote: > > Hi all, > > Is it possible to allow input into a spreadsheet, but at the same time > making sure that data that is inputted is impossible to delete? > > Hope you guys can help, &...

Deleting inactive accounts that have zero balance for entire year
How do you delete inactive accounts that have a zero balance for the entire year? We have an account that is inactive that carried over into 2008 that will have no postings in 2008 that we would like to delete. Thanks for all help in advance. -- Dave Christman System Developer Dave - From GP - Cards|Financial|Account>>>type in the account you want and click the delete button. That is the cleanest way out. "Dave Christman" wrote: > How do you delete inactive accounts that have a zero balance for the entire > year? We have an account that is inactive that car...

Deleted Items Folder in Outlook
Using Outlook from Office XP. When messages are deleted from inbox...they go to the deleted items folder. When the deleted items folder is opened, there is nothing in the folder (yet the folder name may show that there are unread messages in the folder). Any ideas how to see the items in the folder? Sometimes I would like to get the message back... Click View | Current View and make sure it's set to Messages and not to some other view that may have a filter on it. -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please r...

Linking a dinamic cell value from one sheet to another
Hi, I am working on a model where I integrate Microsoft Excel and Microsoft Project. There is a constantly changing resource data in Microsoft project. I have written a VBA code in Project to export the required data into an Excel sheet. The exported data does not comprise of a standard column set; in other words, the number of columns varies according to the necessity. There is a cell containing the total value at the bottom of the last column. This cell value should be linked to a cell in another sheet. Since there is inconsistency in the number of imported columns, the location of t...