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