Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem auto numbering in column



I  have  this  code   merges duplicates  items  based  on  column B  and  summing the  values  in column C  but  I  need  after  merging  should  arrange  the  numbers  again  in  column A  1,2,3.... . I  put  this  line   in  the  end  of  the  code

 Columns(1) = Evaluate("row(1:" & Rows.Count & ")")

  but  the  problem  auto-numbers for  all  of  cells  in  column A  even  if  there  is  no  data  in  column B,  so  what  I  want  just  auto-number  based  on  filled cells  in column B  .

this  is  the  code 

Sub mergeitems()
Dim Rng As Range, Dn As Range, n As Long, nRng As Range, ws As Worksheet
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Dn
        If nRng Is Nothing Then Set nRng = _
        Dn Else Set nRng = Union(nRng, Dn)
        .Item(Dn.Value).Offset(, 1) = .Item(Dn.Value).Offset(, 1) + Dn.Offset(, 1)
    End If
If Not nRng Is Nothing Then nRng.EntireRow.Delete
End With
 Columns(1) = Evaluate("row(1:" & Rows.Count & ")")
End Sub

any  idea  to  adjust  the  code ,-please?



Selected Answer


You should limit it to a range not an entire column. I suggest you change:

Columns(1) = Evaluate("row(1:" & Rows.Count & ")")
Set NewList = Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row)

NewList.Value = Evaluate("row(1:" & Rows.Count & ")")

(where the last row of column B is calculated using Don's tutorial Get the Last Row using VBA in Excel).

If you want to declare NewList, do so as a Range.

Hope this helps.



excellent  !  this  idea  works 
many  thanks  John !
Ali M (rep: 12) Mar 30, '22 at 12:20 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login