Previous Topic Next Topic

Help using Excel Macro To Insert Rows

Closed
Awesom-0 - Feb 19, 2009 at 06:50 AM
 Me - May 16, 2009 at 04:38 PM
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 responses

Answer 1 / 2
Awesom-0
Feb 19, 2009 at 07:06 AM
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
Answer 2 / 2
Me
May 16, 2009 at 04:38 PM
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
3