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

Bill_Biggs

Well-known Member
Joined
Feb 6, 2007
Messages
1,216
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
This should do the trick:
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,326
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top