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..?

Well - I never thought I'd be using the same event procedure twice in 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 number change, and this happens whenever any worksheet calculations update. This is better than putting this in the SheetSelectionChange event, as it would greatly slow down editing of the workbook.

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

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

Mark

I actually got thinking this routine will be useful for me too, so I thought I'd refine it. The following version will take account of AutoFiltering or manual hide/show operations. Also it only changes the row heights that NEED changing, at the end (doing them one by one generates new Calculation events and makes the routine recurse, taking a lot of unnecessary time).

The beauty of this is that if you change any erroneous Order Numbers then re-sort the list, the routine gets re-triggered and alters the row heights to suit - but again, only the heights that actually need to be changed.

Oh yes, and my first version relied on your order range starting at A1. This one doesn't.


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim tVal As Variant
Dim oCalc, z As Integer
Dim xRange(1 To 2) As Range

For z = 1 To 2
Set xRange(z) = Nothing
Next z

With ActiveCell.CurrentRegion
nRow = 0

For Each xRow In .Rows
nRow = nRow + 1

z = 0

If Not xRow.EntireRow.Hidden Then
If .Cells(nRow, 1) < tVal Then
tVal = .Cells(nRow, 1)

With .Cells(nRow, 1).EntireRow
If Not .RowHeight = 24 Then
z = 1
End If
End With
Else
If .Cells(nRow, 1).EntireRow.RowHeight 12 Then
z = 2
End If
End If
End If

If z 0 Then
If xRange(z) Is Nothing Then
Set xRange(z) = xRow
Else
Set xRange(z) = Application.Union(xRange(z), xRow)
End If
End If
Next xRow

End With

If Not xRange(1) Is Nothing Then
xRange(1).EntireRow.RowHeight = 24
End If
If Not xRange(2) Is Nothing Then
xRange(2).EntireRow.AutoFit
End If
End Sub

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

"BizMark" wrote:


Well - I never thought I'd be using the same event procedure twice in
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 number
change, and this happens whenever any worksheet calculations update.
This is better than putting this in the SheetSelectionChange event, as
it would greatly slow down editing of the workbook.

When you print/preview this, it will look as if you have a blank row in
between each order, even though there isn't. This is highly
recommended, as it is best to keep continuous runs of data within one
'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;700466 Wrote:
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

Then you might need to try putting the code into the Workbook_SheetSelectionChange event instead.