Macro to add line between certain rows

[jimmyfinch]

Hi, i need a macro to insert a blank line between rows that are different on column 2.

Example Data...

[tt]
Col 1 Col 2
12345 A
12346 A
12234 A
34534 B
52323 C
23455 C
23455 C
23542 C
[/tt]

Needs to look like

[tt]
Col 1 Col 2
12345 A
12346 A
12234 A

34534 B

52323 C
23455 C
23455 C
23542 C
[/tt]

Please can anyone help!

Many thanks,

JF

 

[Chance1234]

Go to Tools Record Macro and do the actions that you want.

Thats one part of your code.

Now you are going to need to look at identify in VBA which rows you need to apply that action to.

I suggest you look at the Range and Cell Objects



Chance,

F, G + Its official, its even on a organisation chart et all

 

[AnotherHiggins]

Consider using Excel's native functionality.

Data > Subtotals will insert a count for each value in Column 2 at the bottom of that value. You'll also have the ability to look at only the summary data or line item detail. It will wind up looking something like this:
[tt]
Col 1 Col 2
12345 A
12346 A
12234 A
A Count 3
34534 B
B Count 1
52323 C
23455 C
23455 C
23542 C
C Count 4
Grand Count 8

[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I would urge you, in the strongest of ways, NOT to insert empty rows!!!! That is a HUGE mistake, under most circumstances.

Use John's Subtotal approch.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[timbo44]

Hi,

Try this code

Sub insert_blanks()
Dim intR As Integer, MyValue As String
'select range containing first entry in your example range B2
Do Until ActiveCell.Value = ""
MyValue = ActiveCell.Value
Do Until ActiveCell.Value <> MyValue
ActiveCell.Offset(1, 0).Activate
Loop
'This cell contains different value so insert blank cells
intR = ActiveCell.Row
Range(Cells(intR, 1), Cells(intR, 2)).Select
Selection.Insert shift:=xlDown
Cells(intR + 1, 2).Select
Loop
End Sub