Ias there a better way to find changes in a column and act

[Bill_Biggs]

I have written the following code to subdivide data on a page by changes in column A and count those subdivisions. Just wondering if there is a better way to do this.

'Now seperate the data by the CPU's
Range("A1").Select
E = Range("A1").Value
DeltaAlpha:
ActiveCell.Offset(1, 0).Select
F = ActiveCell.Value
If F = "" Then GoTo DeltaBeta
If E = F Then GoTo DeltaAlpha
If E <> F Then
ActiveCell.EntireRow.Insert Shift:=xlDown
G = G + 1 'Counts the number of times that DeltaAlpha subdivides the data
End If

ActiveCell.Offset(1, 0).Select
E = ActiveCell.Value
GoTo DeltaAlpha

DeltaBeta:
End Sub

Thanks ahead of time.

Bill

 

[goldfish]

This should do the trick:

Dim RNG As Range, InsertCount As Long

Set RNG = Range("A1")
Do
    Set RNG = RNG.Offset(1, 0)
    If RNG.Value <> RNG.Offset(-1, 0).Value Then
        RNG.EntireRow.Insert Shift:=xlDown
        InsertCount = InsertCount + 1
    End If
Loop Until RNG.Value = ""

I try and write all my code without using goto's or labels as that is a slow/inefficient way to write. Also using activecell/selection/select can be a downfall as they are not needed.

Hope this helps,
~Gold fish

 

[erik.van.geit]

Hi, Bill,

See this post
http://www.mrexcel.com/board2/viewtopic.php?p=1103515
No "selecting" nor "activating", no loop, so much faster.

I didn't test, but it seems to me that substracting the first "LR" from the second LR will give the number of groups. You would only need to rename them LR1 & LR2 (or any names you like) to calculate the difference.

kind regards,
Erik

 

[Bill_Biggs]

Thank you both. That is very helpful!