Leaving a blank row
 

Sheet 1, first 4 columns have values something like

1,a,b,c
1,a,b,c
1,a,b,c
2,a,b,c
3,a,b,c
3,a,b,c

On report -sheet 2 - I'like to have a blank row between each
different value on column A. Like below

3 rows for 1
1 blank row
1 row for 2
1 blank row
2 rows for 3

I've had no difficulty coding my report so far. But leaving these
blank rows I couldn't manage.
Any helping code snippets will be really appreciated. Thank you.

Leaving a blank row
 

Post your code for review.

--

Regards,
Nigel




wrote in message
...
Sheet 1, first 4 columns have values something like

1,a,b,c
1,a,b,c
1,a,b,c
2,a,b,c
3,a,b,c
3,a,b,c

On report -sheet 2 - I'like to have a blank row between each
different value on column A. Like below

3 rows for 1
1 blank row
1 row for 2
1 blank row
2 rows for 3

I've had no difficulty coding my report so far. But leaving these
blank rows I couldn't manage.
Any helping code snippets will be really appreciated. Thank you.

Leaving a blank row
 

Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
If Cells(X, 1).Value < "" Then
If Cells(X - 1, 1).Value < "" Then
Cells(X, 1).entirerow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP


On Mon, 9 Mar 2009 11:48:13 -0700 (PDT), "
wrote:

Sheet 1, first 4 columns have values something like

1,a,b,c
1,a,b,c
1,a,b,c
2,a,b,c
3,a,b,c
3,a,b,c

On report -sheet 2 - I'like to have a blank row between each
different value on column A. Like below

3 rows for 1
1 blank row
1 row for 2
1 blank row
2 rows for 3

I've had no difficulty coding my report so far. But leaving these
blank rows I couldn't manage.
Any helping code snippets will be really appreciated. Thank you.

Leaving a blank row
 

On Mar 9, 9:34*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 1).End(xlUp).Row
* * Application.ScreenUpdating = False

* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
* * * * * * If Cells(X, 1).Value < "" Then
* * * * * * * * If Cells(X - 1, 1).Value < "" Then
* * * * * * * * * * Cells(X, 1).entirerow.Insert Shift:=xlDown
* * * * * * * * End If
* * * * * * End If
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub

Gord Dibben *MS Excel MVP

On Mon, 9 Mar 2009 11:48:13 -0700 (PDT), "



wrote:
Sheet 1, first 4 columns have values something like

1,a,b,c
1,a,b,c
1,a,b,c
2,a,b,c
3,a,b,c
3,a,b,c

On report -sheet 2 - *I'like to have a blank row between each
different value on column A. Like below

3 rows for 1
1 blank row
1 row for 2
1 blank row
2 rows for 3

I've had no difficulty coding my report so far. But leaving these
blank rows I couldn't manage.
Any helping code snippets will be really appreciated. Thank you.- Hide quoted text -

- Show quoted text -

Gord, thank you so much.