VBA code to set Name for a dynamic range in one column

[Lifeson]

Hi all,

I am brand new to VBA and only just beginning to learn.

I have 13 columns of data in an Excel sheet. Row numbers vary from 40,000 to 500,000.

Column 13 (M) may contain duplicates which I want to count in column 14 (N) and paste to a new sheet (not delete).

I had attempted to name the range in column M(13) then reference range in a countif or vlookup but since the volume of data varies wildy, I couldn't figure out how to name the range dynamically without referencing the specific cell ranges (M2:M44927) - I need to run this report daily but would prefer to automate with a module (not within an activated worksheet).

Would anyone have some VBA code I could re-use to do this?

 

[Fluff]

Really should check my code before posting it

Try

For Each Ky In .Keys
         If .Item(Ky)[COLOR=#ff0000]>[/COLOR] 1 Then
            rr = rr + 1
            Nary(rr, 1) = Ky
         End If
      Next Ky

 

[mikerickson]

My version is a semi-standard way to define a Dynamic Named Range which varies with the data on the sheet.
It includes all the entries in column M (caveat for formulas that return ""). It does not exclude duplicates. But it does identify those cells that need to be checked.

 

[Lifeson]

Thanks again Fluff - On reading that, it does make sense. I need to read up on scripting dictionary as well, it seems to be really useful. Thanks also Mike!

 

[Fluff]

Glad to help & thanks for the feedback

 

[Lifeson]

I think people will be getting bored with me very quickly. I am using the latest VBA provided by FLUFF (thanks again). I have one query; When I run the code and there is a duplicate, it returns a "2" for each entry. Is there a way to show "1" for the 1st entry then when a duplicate is found it will display "2" for the recurrence of the original?

 

[Fluff]

Try

Sub Lifeson()
   Dim Ary As Variant, Nary As Variant, Ky As Variant
   Dim r As Long, rr As Long
   
   With Sheets("Sheet1")
      Ary = .Range("M2", .Range("M" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 2)
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         .Item(Ary(r, 1)) = .Item(Ary(r, 1)) + 1
         Ary(r, 2) = .Item(Ary(r, 1))
      Next r
      Sheets("Sheet1").Range("M2").Resize(UBound(Ary), 2).Value = Ary
      For Each Ky In .Keys
         If .Item(Ky) > 1 Then
            rr = rr + 1
            Nary(rr, 1) = Ky
         End If
      Next Ky
      Sheets("Sheet2").Range("A2").Resize(rr, 2).Value = Nary
   End With
End Sub

 

[Lifeson]

That is absolutely brilliant - I need to take time to go through both to see where the differences lie to see if I can figure what you did.

I'm really very grateful - Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback