Attn: Faster

Posted by Anthony on July 23, 2001 5:22 AM

Hello faster! Last week you posted a code which would allow blank rows to be inserted upon the conditions of certain criteria.

After I subtotal a spreadsheet, I would like to input blank rows after each of the sub-totals on my report. Is this possible? Here is the location of your code:23203.html

Any assistance would be greatly appreciated.

Thanks

Anthony

Posted by faster on July 23, 2001 8:09 AM

Try this on a copy of your worksheet

Sub InsSub()
'you should selection the entire sheet when
'removing subtotals
'you will have a blank row between group
'when subtotals are removed

'assumes subtotals are now in column A
'change as needed
Range("A1").Select

Dim SubRow
Cells.Find(What:="total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
SubRow = ActiveCell.Row

Range("A1").Select
Do While Selection.Offset(0, SubRow) &LT;&GT; ""
If Selection = "" Then
Selection.Offset(1, 0).Select
Selection.EntireRow.Insert
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

End Sub

Posted by Anthony on July 23, 2001 12:22 PM

Error Message???

Thanks for the response faster! An error message apprears upon running the macro. The following line becomes highlighted after debugging:

Do While Selection.Offset(0, SubRow) &LT;&GT; ""

Also, should I copy the code exactly as you wrote it?

Thanks Again

Anthony

Posted by faster on July 24, 2001 9:55 AM

Re: Error Message???

Sorry Anthony, I transposed rows and columns.
Try this, just copy and paste the code.

Sub InsSub()
'you should selection the entire sheet when
'removing subtotals
'you will have a blank row between group
'when subtotals are removed

'assumes subtotals are not in column A
Range("A1").Select

Dim SubCol
Cells.Find(What:="total", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
SubCol = ActiveCell.Column - 1

Range("A1").Select
Do While Selection.Offset(0, SubCol) &LT;&GT; ""
If Selection = "" Then
Selection.Offset(1, 0).Select
Selection.EntireRow.Insert
Selection.Offset(2, 0).Select
Else
Selection.Offset(1, 0).Select
End If
Loop

End Sub