Solved: separate rows with blank rows

[ndendrinos]

Went to the archives and found this code from Ann Troy / (approved by mdmackillop)


Select last row in worksheet.
Selection.End(xlDown).Select

Do Until ActiveCell.Row = 6
'Insert blank row.
ActiveCell.EntireRow.Insert shift:=xlDown
'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop
End Sub

The code works wonders as long as there are 2 or more rows to separate.
I've included it in my lengthy code that does a filter on a list and copies the visible rows to a sheet called "statement"
And here lies the problem ... Some customers have only one row on their statement and while the code does not create a problem it takes several seconds to execute.
So can an IFNOT be introduced here to the effect that if there is only one row (starting in row6) the sub should exit.

Many thanks

[lucas]

maybe a different method:
Sub Insert_Row()
Dim a As Byte
Dim c As Integer
[A1].Select
a = 2
c = 0
While ActiveCell.Value <> ""
c = c + 2
ActiveSheet.Rows(c).Insert Shift:=xlDown
ActiveCell.Offset(a, 0).Select
Wend
End Sub

[Bob Phillips]

I am loathe to continue code that does selecting like that, but just to change thata code to do as you ask



'Select last row in worksheet.

Selection.End(xlDown).Select

If Selection.Row > 6 Then

Do Until ActiveCell.Row = 6

'Insert blank row.
ActiveCell.EntireRow.Insert shift:=xlDown
'Move up one row.
ActiveCell.Offset(-1, 0).Select
Loop
End If

[ndendrinos]

hello lucas, 4:30AM ... wife No 1 is going to kill me!!
I've spent 8 hours trying your solution and all kind of others to no avail.
Your code worked but did not space the rows in "statement" the way I want them to.
here is the file ... it works with all kinds of "work arounds"
If you get to see it and would like to take over and write it properly please do if not I'll just keep it the way it is and call it solved.
Thank you, Nick

By the way in the form pls type the dates as 09012009 and not 09/01/2009

[ndendrinos]

I don't believe it xld ... was typing my reply to lucas ... allow me to have a go at it in 8hrs from now ... got to sleep sometimes.
Thanks , Nick

[Bob Phillips]

I looked at your workbook, and whilst there is a ton of tidying up that can be done, removing al of that unnecessary selecting, I failed to see where the code that you posted above was, so I am unsure as to where you would insert it.

[ndendrinos]

Perfect xld ... I will use it by introducing a:
[a6].activate at the top.
for if I don't the code takes an eternity to run (like Ann's)
Thank you

for the benefit of others: the line "If Selection.Row > 6 Then" can be eliminated from the sample's code.
the selection must be introduced (here A1) unless of course you can select manually.

So both code Ann's and xld's are one and the same in that regard.... what am I saying? ... they are the same code.

[mdmackillop]

If this is simply for appearance, I would consider increasing the row height rather than inserting additional rows.

[ndendrinos]

and why didn't I think of that? would have saved 5hrs of sleep and the matrimonial cold shoulder this morning.