Solved: VBA code not working

[nvnazeer]

Hi! Xld,
I need your expert help again please. I used the below VBA code provided by you. It seemed to work until I hit a snag in one of my spreadsheets.
For some reason the macro is not working on one of my spreadsheets. I have traced down the error to the attached extract from my spreadsheet. I tried my best to sort this out, but could not. I need some help please. After you run the macro you can see that the total quantity does not match and also there are still some line with same CMN Part No.
If you can tell me the reason then I can maybe re-format the spreadsheet to avoid such occurence in future.

Thanks

Nazeer

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 2 To LastRow

If .Cells(i, "A").Value <> .Cells(i - 1, "A").Value Then

.Cells(i, "C").Value = Application.SumIf(.Columns(1), .Cells(i, "A").Value, .Columns(3))
Else

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
End With

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub kind Forum member

[Norie]

Nazeer

What is the code actually meant to do?

At no point in the code is rng being set to reference a range.

[Bob Phillips]

Mr Nazeer,

My original code was based upon the premise that the data was orted, so all of the same part nos would be contiguous. This sheet has the same part no scattered throughout.

This version shoud work whether they are contiguous or not

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim StartRow As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
StartRow = 1
For i = 2 To LastRow

If Application.CountIf(.Range("A1").Resize(i), .Cells(i, "A").Value) = 1 Then

.Cells(i, "C").Value = Application.SumIf(.Columns(1), .Cells(i, "A").Value, .Columns(3))
Else

If rng Is Nothing Then

Set rng = .Rows(i)
Else

Set rng = Union(rng, .Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete
End With

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

End Sub
[/vba]

[nvnazeer]

Thanks for the reply. What I had meant was that the spreadsheet would be sorted by "CMN Part No" and not by "PartNum". Actually when this is sorted by "PartNum" , automatically the spreadsheet is also sorted by "CMN Part No." as these two has to be the same for the same description. However for some reason this was not the same. That is why the VBA code created problem. As the "CMN Part No"(Column - E) is more critical for me, can you please send me the VBA code based on sorting by "CMN Part No" .
You have been really a big help and I appreciate this last bit of support.
Please refer to spreadsheet attached in this post earlier on.

Nazeer

[Bob Phillips]

Have you tried my re-posted code?

[nvnazeer]

Yes I have tried that. It works fine. However the CMN Part No. is critical for me. Since we have now found that sorting by PArtNum does not essentially sort by CMN Part No. I need the same VBA code based on sorting by CMN part No.
This is also because it is the quantity against the same "CMN Part No". that needs to be automatically added by the VBA code(and not the PartNum) and replace this value with the added quantity on the first row and then delete the rows below(having the same "CMN Part No.') for which the quantities are now added to already in the first row.
Please send me the amended VBA code for one last time(hopefully).

Thank you

Nazeer N.V

[nvnazeer]

please !

[nvnazeer]

I managed by switching the columns of CMN Part No. and PartNum.

Thanks for all the help.