Comparing column A with over 8000 lines to 7 other columns

[Christine Butler]

I need to know how to get specific data back. Column A has over 8000 lines
Columns b:h each have over 8000 lines of data. I need to match column
A with the same number (remember there are 8000 different or matching numbers in Column A) with columns b:h for any of the same numbers that may be in Column A. Can anyone help?
Thanks,
Chris

 

[Oaktree]

Welcome to the board.

So... what would you like to happen if/when a match is found in, say, column D?

 

[Christine Butler]

Thanks for trying to help.

All of the results that match would need to be in either some column or just sorted out so I know which ones to remove.

 

[Oaktree]

Still a couple more questions...

Are you removing them from column A or from columns B:H?

Do you just want the cell's contents cleared? Or do you want the data to shift?

 

[Christine Butler]

Comparing

I need to compare column A to columns b-i. If there is a match from the data in column a anywhere else in the worksheet, I need to delete the match that would be anywhere in columns b-i.
Thanks again,
Chris

 

[Oaktree]

One way is:

For Each cell In Range("B1:I"&Range("I65536").end(xlup).row)
    Set my_match = Range("A:A").Find(cell)
    If Not my_match Is Nothing Then cell.ClearContents
Next

 

[Christine Butler]

I'll try it.

Dop I enter it exactly as you described?

 

[Oaktree]

More or less... just put it within your sub.

Such as:

Sub my_replace()
Dim cell As Range
For Each cell In Range("B1:I" & Range("I65536").End(xlUp).Row)
    Set my_match = Range("A:A").Find(cell)
    If Not my_match Is Nothing Then cell.ClearContents
Next
End Sub

 

[Christine Butler]

Thanks

Thanks for trying to help, but I'm obviously not that experienced with Excel.
I will try to play around with it. Thank you again.
Chris