Help using Excel Macro To Insert Rows [Closed]

Report
-
 Me -
Hi,

I currently have over 7000 rows of data, displayed similarly to this:
Iten: Value Value Value
Apples x y z
Apples x y z
Apples x y z
Bananas x y z
Bananas x y z
Bananas x y z
Bananas x y z
Oranges x y z
Oranges x y z

Basically I need each different Item to be seperated by two rows, so after the "apple" rows there are two empty rows, then the "banana" rows, then two empty rows, then the "oranges" etc etc and continue write down to the end of the data (as mentioned, about 7000+ rows down!)

I hope I have explained clearly what I need doing? I assumed it would be something like
if active.cell = "cellabove" then activecell.offeset (1,0)
Else "insert row"

Thank you in advance for any help I recieve!

2 replies

Apologies, having tried again I have figured out where I was going wrong! I hope I have not wasted anyones time!

If anyone is interestet at all, the macro is:

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Offset(1, 0).Select
Else
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If


End Sub

You can just hold down the macro shortcut and it will keep on going till it can go no more

Again, very sorry for wasting peoples time
6
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Instead of holding down the shortcut key loop through the code. This method assumes when running the code there are no empty rows between data. Otherwise a for loop would work

sub Add_Spces()
Check:
if isempty(Activecell) or Activecell = 0 then Exit Sub

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
ActiveCell.Offset(1, 0).Select

Else

Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If
goto: Check

End Sub

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!