Excel search/find in same sheet issue

Hi guys! In my quest for an answer to my issue I came upon your site. Seems like the most promising forum to go to regarding VB issues. I hope my question is an easy one.
I created a macro to copy an entry in cell C1, search for it in column A, delete it, shift cells up, go back to C1, delete its entry, shift cells up and do the process over again. I have a list of 1700+ entries in column A (primary). I have a list of 600+ entries in column C that is a list of disabled users. I would like to delete the disabled users from column A, which would, in turn, effectively truncate it down to about 1,100+ entries in column A. All entries in column C are unique. If it'll help, here's the code within the macro:

Sub CleanUp()
'
' CleanUp Macro
' Referencing column C, find duplicates in column A and delete them.
'
'
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range

If WorksheetFunction.CountA(Cells) > 0 Then
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select

LastRow = Cells.Find(What:="SAMPLE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-6, 2).Range("A1").Select
Selection.Delete Shift:=xlUp
ActiveCell.Offset(0, -2).Range("A1").Select
End If
End Sub


I thought it might be an issue with the Find code (highlighted in blue above), but I don't know if there's a way to tell it to copy and search for whatever it had just copied from the cell entry. Do you guys know of a way? Or could there be an even better way to do this?

Thank you very much for your time :)

Hi Cozmo, welcome to the forum! :)

All entries in column C are unique.
Are the entries in column A also unique?
Is column A sorted? If yes, how?
If not, does it matter if it's reordered during this process?

Thank you for the welcome! :)
Technically, all entries in column A are unique, except that the entries in column C would be considered duplicates in A because they're the ones that need to be deleted in column A. I have to use column A exactly the way it is, but it is sorted alphabetically. I cannot reorder it beyond its default, unfortunately.

Hi Cozmo1 and welcome to the forum! :)

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

A very quick way to do this (because it avoids looping) would be as follows.

Add a 'disabled' indicator into a spare column on your table to indicate if the names are contained within the disabled column, eg: (or something to the same effect)
Worksheets(1).Range("B2:B1600").Formula = "=COUNTIF(Source!$C:$C,A2)"
Ensure that the formulas have calculated. (worksheet object calculate method)
Worksheets(1).Calculate
Use autofilter to filter for names that are disabled per the indicator you just added. (look at range object's autofilter method in the Excel VBA helpfile).
Delete the disabled names. (check out the range object's specialcells method and the xlCellTypeVisible constant in the Excel VBA helpfile).
eg.

'this assumes there is a header row
Worksheets(1).AutoFilter.Range.Offset(1, 0). _
SpecialCells(xlCellTypeVisible).EntireRow.Delete

Clear indicators and remove autofilter.


If you're deleting rows you might want to keep your disabled names list on a separate sheet?

HTH
Colin

If you're deleting rows you might want to keep your disabled names list on a separate sheet?

HTH
Colin

That's actually a good idea. Just figured it would be a burden to you guys once I hit the same Find command problem.

That's actually a good idea. Just figured it would be a burden to you guys once I hit the same Find command problem.

lol Cozmo1... thank you! I'd like to think the rest of the suggestion is a good idea too! ;)

Have a go and let us know if you get stuck! :)

Colin

This could also be done with array formulas (without VBA), although in a rather convoluted way. If you put this
=INDEX($A$1:$A$9,MATCH("?*",IF((1-COUNTIF(C:C,$A$1:$A$9)),$A$1:$A$9,""),0))
into cell B1, this
=INDEX($A$1:$A$9,MATCH("?*",IF((1-COUNTIF(C:C,$A$1:$A$9))*(ROW($A$1:$A$9)>MATCH(B1,$A$1:$A$9,0)),$A$1:$A$9,""),0))
into cell B2 (array-entered) and then fill down, column B will contain those entries from range A1:A9 that aren't found in column C, in order, and "N/A" errors below. To extend the range, replace A9 by the address of the last cell in column A in both of the above.

ETA: Warning: This is quite inefficient... :pCheers, Colin! :)

Wow! Judging from your examples, I definitely have some to learn regarding VB and VBA. I'll have the opportunity to test both suggestions when
I get to work tomorrow. Thank you very much! :)

You're welcome Cozmo1. :)

Note that the second array formula is missing a bracket:
=INDEX($A$1:$A$9,MATCH("?*",IF((1-COUNTIF(C:C,$A$1:$A$9))*(ROW($A$1:$A$9)>MATCH(B1,$A$1:$A$9,0)),$A$1:$A$9,""),0))

Of the three outlined choices, my strong recommendation is the filtering option - even though it may take you a little time to write the code yourself to do it.

Colin