Find and Mark data

[RWRose]

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.

 

[venkat1926]

there are three macros
test
numbering
fullmacro

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

the macros are

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

 

[stanleydgromjr]

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]

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.