Adding a row between 2 rows with all caps

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
173
Von Pookie and XLGibbs previously helped me with this delima and got me 99% there. I thought I would post again because I've yet to be able to figure this one out.

Situation is this:
I have a financial statement that dumps into excel, however it places the total of one section directly above the header of the next. For example column A will contain:

TOTAL REVENUES
OPERATING EXPENSES

I need to be able to find when two cells in a row are all caps and place a blank row between them.

This is the solution I've been working with:

Sub test()
Dim LastRow As Long, i As Long
Dim testStr As String, testStr2 As String

LastRow = Range("A65536").End(xlUp).row
i = 1

Do 'Begin loop

testStr = Cells(i, 1) 'strings the row
testStr2 = Cells(i + 1, 1) 'strings the next row
If Not testStr = "" Then
If testStr = UCase(testStr) And _
testStr2 = UCase(testStr2) Then 'compares both strings to UPPER case
'and returns true if the testStr = the Upper case version of the string.
'Add row after between the two if true

Rows(i + 1).EntireRow.Insert

'increment variables by 1 since row was added
i = i + 1
LastRow = LastRow + 1
End If
End If
i = i + 1 'next row

Loop Until i > LastRow

End Sub


The problem arrises when I have one part of the report that DOES put a blank row between the total line and the header of the next and this piece of code puts an ADDITIONAL BLANK ROW between them. I only need it to put a blank row when one does not already exist.

Any ideas are greatly appreciated.

SpaceCap
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

timorrill

Well-known Member
Joined
Sep 20, 2006
Messages
528
Try using

If Not testStr = "" And Not testStr2 = "" Then...

This will make sure that neither testStr is blank before inserting a row between them.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello SpaceCap,
If I understand your situation then you should be able to get away with just
changing this line:
If Not testStr = "" Then
to:
If Not testStr = "" And Not testStr2 = "" Then
 

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,262
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top