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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,884
Messages
5,543,023
Members
410,583
Latest member
gazz57
Top