# Adding a row between 2 rows with all caps

#### spacecaptainsuperguy

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Try using

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

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

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

you guys are AWESOME. that fixed it right up.

thanks a lot.
SpaceCap

Replies
0
Views
121
Replies
3
Views
563
Replies
1
Views
400
Replies
4
Views
301
Replies
3
Views
678

1,219,961
Messages
6,151,170
Members
451,012
Latest member
needvbahelp1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back