Insert Row in Excel Spreadsheet

[AlanCurtis]

I have an Excel 2000 spreadsheet that contains order details. The number of rows varies for different orders - some will have only one row, others may have several.

Is there a way to look at the Order Number column and, each time the value changes, to insert a blank row between the orders?

Thanks in advance!

 

[xlbo]

To do this without code, try out the data>Subtotals wizard (although this will put a value in at least 1 cell of the row for the subtotal)

Via code, you would need a loop like:
For i = range("A65536&quot.end(xlup).row to 2 step -1
tRow = range("A" & i).text
nRow = range("A" & i-1).text
if nRow<>tRow then
rows(i & &quot;:&quot; & i).insert shift:=xlDown
else
next i

assuming order numbers in column A Rgds
~Geoff~

 

[AlanCurtis]

Geoff,

Thanks very much. Yes, I can do it with Subtotals, but it's kinda klutzy, and I would love to see how it's done in code.

Please understand, I am a real newbie to VBA. When you suggested doing it &quot;via code&quot;, I took that to mean that I could create a macro called something like &quot;InsertRow&quot;, then just cut-and-paste your code into it between &quot;Sub InsertRows()&quot; and &quot;End Sub&quot;.

(BTW I did move the order numbers from column B to column A, just in case simply changing &quot;A&quot;s to &quot;B&quot;s in your code would not do the trick.)

When I tried to run the macro, I got an error message:

Compile error:
Next without For

I'm sure I am doing something fundamentally wrong, but haven't a clue as to what it is. Please set me straight!

Thanks again,
Alan

 

[par60056]

The real compile error is &quot;If without endif&quot;.

Looking at the code bit supplied change the &quot;else&quot; to &quot;end if&quot;

Peter Richardson

 

[xlbo]

Apologies - wrote it on the fly inna bit of a rush -

Sub InsertRows()
dim tRow as string, nRow as string, i as long
For i = range(&quot;A65536&quot.end(xlup).row to 2 step -1
tRow = range(&quot;A&quot; & i).text
nRow = range(&quot;A&quot; & i-1).text
if nRow<>tRow then
rows(i & &quot;:&quot; & i).insert shift:=xlDown
else
end if
next i
end sub

Please note tho that this may take a while, depending on the number of rows you have to go thru
Rgds
~Geoff~

 

[AlanCurtis]

Geoff, Peter,

Thanks very much for the help. It works perfectly, and it sure is a lot easier than doing subtotals and then having to clean up afterward!

Alan