macro for inserting one row between different data

  • Thread starter Thread starter eva cheng
  • Start date Start date
E

eva cheng

Hi,

I am using excel 2003, my file looks like below

order no invoice no amount
123 S1000 $10
234 S1000 $20
456 S1001 $30
123 S1001 $40

I want to use a macro to insert a blank row to separate different invoice
no, how to write it ?

Thanks
eva cheng
 
Hi,

I have assumed your invoice numbers are in column B

Sub Insert_Rows()
'insert a row at every name change in a column B
Dim X As Long
Set Sht = Sheets("Sheet1")
MyColumn = "B"
For X = Sht.Cells(Rows.Count, MyColumn).End(xlUp).Row To 3 Step -1
If Sht.Cells(X - 1, MyColumn) <> Cells(X, MyColumn) Then Rows(X).Insert
Next X
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
I suggest NOT inserting blank rows in you table and simply increase row
heights instead.
 
Back
Top