How to add an empty row after each data set of .

[Robert Hyde]

I an working on a sheet containing orders data. Each Order number contains
more than one items. What I want to do is, add an empty row after each order
number appearing on multiple rows. Suppose, row # 1 to row# 10 containng
order #123 and order #124 appearing on row # 11 to 15. Now I wan to add an
empty row once order number changed. So an empty row should added after row
#10.. Is there any possibility to do this..?

 

[BizMark]

Well - I never thought I'd be using the same event procedure twice i
one day, but here it is: put this in the 'ThisWorkbook' module:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim tVal As Variant

With ActiveCell.CurrentRegion
For Each xRow In .Rows
If .Cells(xRow.Row, 1) <> tVal Then
tVal = .Cells(xRow.Row, 1)
.Cells(xRow.Row, 1).EntireRow.RowHeight = 24
Else
.Cells(xRow.Row, 1).EntireRow.AutoFit
End If
Next xRow
End With
End Sub


What this ACTUALLY does is increase the row height at each numbe
change, and this happens whenever any worksheet calculations update.
This is better than putting this in the SheetSelectionChange event, a
it would greatly slow down editing of the workbook.

When you print/preview this, it will look as if you have a blank row i
between each order, even though there isn't. This is highl
recommended, as it is best to keep continuous runs of data within on
'region' - i.e. with no broken rows or columns.

This routine only works if your order number is in the first column.

Mark

 

[Robert Hyde]

Thanx, but its not working. By the way I am using Excel 2003. Following might
clear myself...

Order Number Item Price
123 ABC $22.00
123 BCD $33.00
123 CDE $44.00
125 DEF $55.00
125 EFG $66.00
129 FGH $77.00
129 GHI $88.00

I want Excel to add an empty row automatically after row# 3 and row#5
because Order numbers have been changed...

Robert