Posts by JohnnyBeck

    Re: find duplicates with Vba when cells have more than 255 characters

    Both of these are working with my initial test sheet. Thank you very much PCI and Jindon!

    While I like the fact that Jindon's code gives different colors to make it easy to identify different sets of duplicates, this will be run on some massive workbooks that would have the potential to get really crazy looking. So I think I'll use PCI's solution initially.

    Thanks again guys!

    Hello all. I have been developing a fairly involved macro that includes a portion to identify when an entire row has been duplicated somewhere in the worksheet. I have used a countif function with success.

    However, if the entire row's data totals more than 255 characters, the Countif function craps out on me.

    So here's the question, does anyone have a way to check for duplicates (highlight them) in VBA when the cell contents is over 255 characters?


    Re: Copy row based on cell value

    If you have more than one cell in a row that meets the criteria, it will copy that row multiple times to the second sheet. Do you have any type of index number or unique identifier for each of the rows? If so, you can add code to remove the duplicates from the second sheet so you'll only see them once.

    Re: Copy row based on cell value

    Try this friend:

    Re: Compare data in multiple columns across 2 worksheets

    Sub multmatch()Dim cell As RangeDim found As RangeDim nameColDim totalrows As Longtotalrows = ActiveSheet.UsedRange.Rows.CountnameCol = "$U$2:$U$" & totalrows For Each cell In Sheets("Sheet1").Range(nameCol)Set found = Sheets("TBP").Range(nameCol).Find(cell, LookAt:=xlWhole)For Each found In Sheets("TBP").Range(nameCol)If found & found.Offset(0, -16) & found.Offset(0, -18) Like cell & cell.Offset(0, -16) & cell.Offset(0, -18) Thencell.Offset(0, 5).Value = found.Offset(0, 5).Valuecell.Offset(0, 5).Interior.Color = 65535
    End IfNext foundNext cellEnd Sub

    Re: Compare data in multiple columns across 2 worksheets

    Ok. This code works with your layout. However, be aware that I had to remove the timestamps in order for the dates to "match" since I'm comparing values of cells. I did that quickly using Text To Columns on the data tab in Excel. Also, on your example sheet, there were NO matches of data. I had to create a match or two to make sure the code was working with your layout. Either way, here it is:

    Re: „Not enough system ressources to display completely"

    I'm sorry I don't still have the link. But, I learned about the issue from Microsofts website. It's apparently an issue with Excel and how it handles resources. I had done a general search with the error I was getting at Google and found Microsofts page that addressed the issue.

    Re: Compare data in multiple columns across 2 worksheets

    Is the second sheet longer than the first?

    Also, I thought you said you wanted column Z from the second sheet to be placed in column AN on the first sheet. Is it the other way around?

    I just looked at your scrubbed sheet. Dude, the names are not in column C as you said they were.

    Am I now looking at an accurate picture of the sheet you want? Are you wanting to use the names in column K to be the basis of the comparison??

    I can do this, but I need accurate directions bro.

    Re: Compare data in multiple columns across 2 worksheets

    Assuming that the Name (which is your constant) is in column C, this should work:

    Re: Compare data in multiple columns across 2 worksheets

    Quote from lawsonrw;560267

    Okay, so I changed the code to

    The change I needed to make was to put a space between "Sheet1" to make it "Sheet 1" Sorry for the confusion. That said, it appears the script will execute THANKS A TON! But where is the output? I hit run, it did its thing for a while, but I don't see anything different. Do I need to have a certain cell highlighted or is there some other step I'm missing?

    Now that you have the error fixed, we'll still need to adjust for the columns NOT being A, B, C, D as you stated in your original example. This is why specifics make such a huge difference. I'll adjust the code and post it in just a few minutes.