Posts by FaceMetric

    There probably is a better way but you could format the Cell D1 to Text and the Zero will then be kept.


    Code
    1. Dim MyCell As Range, MyRange As Range
    2. Set MyRange = Sheets("Sheet1").Range("A1", Range("A" & Rows.Count).End(xlUp))
    3. Set MyRange = Range(MyRange, MyRange.End(xlDown))
    4. For Each MyCell In MyRange
    5. Sheets.Add After:=Sheets(Sheets.Count)
    6. Sheets(Sheets.Count).Name = MyCell.Value
    7. Sheets(Sheets.Count).Range("D1").NumberFormat = "@"
    8. Sheets(Sheets.Count).Range("D1").Value = MyCell.Value
    9. Next MyCell

    Re: Finding Duplicate Values in the Same Columns


    Kjbox...thanks once again. Had to tinker with it slightly. I'll let you off the typo :wink:. The loop also ended in a run-time error 9 'subscript out of range'. This I know was because the loop exceeded the assignment in the array. I fixed with a rudimentary error catch which I'm sure can be improved but seems to work.


    So this is what I ended what with some with FindDuplications2 but also manage with FindDuplications3. The really great thing is that this ran in 1 second for 25k rows. So again many many thanks for your help. Has been greatly received.

    Re: Finding Duplicate Values in the Same Columns


    I was wondering that as since my duplicates are all together and not randomly in the worksheet that breaking the loop when a non-duplicate is found would make it faster. I've been running it for 30 minutes so a bit too slow for me.


    I'll need a clear head but will look to see how to do that when I have.

    Re: Finding Duplicate Values in the Same Columns


    KjBox, Thank you for welcoming me. Hadn't realised this was my first post.
    That words a treat and does what I want in the first instance. However, how would I change this so that the first item in the range is marked? So let's say Col A & Col B are duplicates. The code marks B but would like A as well. Ideally, each batch of duplicates I would like marked as Duplicate1,Duplicate2. An example below.
    01/01/01 12:00 1234 5678 Duplicate1
    01/01/01 12:00 1234 5678 Duplicate1
    01/02/01 12:00 15678 234
    01/03/01 12:00 15678 adde Duplicate2
    01/03/01 12:00 15678 adde Duplicate2
    01/03/01 12:00 15678 adde Duplicate2


    Am I too demanding :) ?

    Hi Guys,
    I found some code at https://analysistabs.com/vba/find-duplicate-values-column/ which marks duplicates in rows in in Col A.


    I would like to expand on this and look at Cols A thru D but can't work out to do that. I decided to CONCATENATE the 4 cols but would prefer not to.


    The original code is below and presume

    Code
    1. WorksheetFunction.Match(Cells(iCntr, 1), Range("A1:A" & lastRow), 0)


    could be changed to

    Code
    1. WorksheetFunction.Match(range(Cells(iCntr, 1),Cells(iCntr,4)), Range("A1:A" & lastRow), 0)


    but this give an Excel VBA Run-time error '13' Type mismatch. So left scratching my head. Any help most appreciated.



    What I was hoping was to match on Cols A thru D