Search : in
By :

Help using Excel Macro To Insert Rows

Last answer on May 16, 2009 9:38:49 pm BST Awesom-0, on Feb 19, 2009 11:50:28 am GMT 
 Report this message to moderators

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!

Configuration: Windows XP
Internet Explorer 7.0

Best answers for « Help using Excel Macro To Insert Rows » in :
Inserting an animated gif in Excel ShowInserting an animated gif in Excel To insert an animated gif image in an Excel spreadsheet, you must insert the image into a control. To insert the control, go to the View menu/Toolbars/Control Toolbox Activate the first button Design...
Excel – Macro to detect and hide blank rows ShowExcel – Macro to detect and hide blank rows Issue Solution Note Issue I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will...
Excel – Macro for copy & paste selected range ShowExcel – Macro for copy & paste selected range Issue Solution Note Issue I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want...
Ask your question Reply

1

Awesom-0, on Feb 19, 2009 12:06:37 pm GMT
  • +1

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

   
Reply to Awesom-0

2

 Me, on May 16, 2009 9:38:49 pm BST

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

   
Reply to Me
Ask your question Reply
They need your help