More efficient code to Loop 2

[Mantle51]

Hello,
The following loop seeks to sum values in col E for which identical text exists in col A. Is there a more efficient way to run this perhaps using For Next statement?
Thanks...

Sub JNLmacro()
Dim rng As Range, counter As Integer
Range("A2").Select
counter = 0

Do
Do
If ActiveCell.Value = ActiveCell(2, 1).Value Then
ActiveCell.Offset(1, 0).Select
counter = counter + 1
End If
Loop Until ActiveCell(2, 1).Value <> ActiveCell.Value

ActiveCell.Offset(1, 0).Select
Selection.EntireRow.Insert

ActiveCell(1, 4).Value = "=SUM(D" & ActiveCell.Row - 1 & "" & (ActiveCell.Row - 1) - counter & ")"

ActiveCell.Offset(1, 0).Select
counter = 0

Loop Until ActiveCell.Value = ""
End Sub

 

[SkipVought]

Hi,

Avoid the Select method...

How Can I Make My Code Run Faster? faq707-4105

Skip,

_[red][/red]

 

[AnotherHiggins]

I don't see anything in the code that compares columns E & A. (In fact, you are adding data to column D, not column E).

What your code seems to do is recreate the Subtotals feature in Excel.

I'm pretty sure the following will do the same thing that you accomplish with your code:

- Go to Data > Subtotals.
- At each change in: HeaderOfColumnA*
- Use function: Sum
- Add Subtotal to: HeaderOfColumnD*

*replace with whatever your corresponding headers are.

If you want efficient code to do this without looping, turn on your macro recorder and follow the above steps.

If you really want to do something where you compare data in two columns - which your code does not do - then please post specific goals you wish to accomplish along with some sample data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Mantle51]

Thank you both for your helpful replies.

Mick