Adding a row between 2 rows with all caps

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
202
Office Version
  1. 365
Platform
  1. Windows
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Try using

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

This will make sure that neither testStr is blank before inserting a row between them.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top