Find and Mark data

RWRose

New Member
Joined
Feb 20, 2006
Messages
17
1st, How can I insert a row each time the data in column A changes?

2nd, How can I number the above rows with consecutive numbers for the first entry only?

Example:

1 abc
abc
abc

2 123
123
123
123

3 bcd

4 234
234
234
234
234

Please help, I have @ 1,300 items with @ 23,000 rows of data to look at.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
there are three macros
test
numbering
fullmacro

It is enough if you run "fullmacro" as it incorporates the other two macros.

the macros are
Code:
Sub test()
Dim j, k As Integer
Columns("a:a").Delete
ActiveSheet.UsedRange.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Rows.Delete
'the above lines are to undo the macro resuls
'for testing once or twice.
k = Cells(Rows.Count, "a").End(xlUp).Row
For j = k To 2 Step -1
Cells(j, "a").Select
If Cells(j, "a") <> Cells(j - 1, "a") Then Cells(j, "a").Rows.Insert
Next
End Sub

Sub numbering()
Columns("a:a").Columns.Insert
Dim i, j, k As Integer
i = 1
k = Cells(Rows.Count, "b").End(xlUp).Row
Cells(1, 1) = 1
For j = 2 To k
If Cells(j, "b") = "" Then GoTo line1
If Cells(j, "b") <> Cells(j - 1, "b") Then
i = i + 1
Cells(j, "a") = i
End If
line1:
Next
End Sub


Sub fullmacro()
test
numbering
msgbox "fullmacro over"
End Sub
 
Joined
Jul 30, 2006
Messages
3,656
RWRose,

I have a macro for you.

What is the Sheetname where your data exists?

What is the first row in column 'A' where your data begins?


Have a great day,
Stan
 

RWRose

New Member
Joined
Feb 20, 2006
Messages
17
Stan,

Sorry to take this long, but I have been involved in other matters.

The sheet name will change frequently so I don't have a set name but I can create whatever it takes.

The data will start in row 2.

Thanks for your help.
 

Forum statistics

Threads
1,141,427
Messages
5,706,380
Members
421,446
Latest member
rena jhon

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