i =  5 
      Do
  If Mid(Cells(i,  8 ),  1 ,  19 ) <> "TOTAL OTHER REVENUE" Then
 
             If Mid(Cells(i,  8 ),  1 ,  13 ) = "OTHER REVENUE" Then
                    Call DeleteRow(CStr(i) + ":" + CStr(i))
             End If

  End If


  i = i +  1 
Loop Until i = iRows

	RESIDENTAL RENT	
- 1 . 8 	Residential Rent-Stabilized	             - 1192865 
 26 . 3 	Residential Rent-DeStabilized	- 1193052 
 11 . 7 	TOTAL RESIDENTAL RENT	             - 2385917 
	RENT REVENUE	
 11 . 7 	Total Residental Rent	             - 2385917 
 3 	Commercial Rent	                          - 108643 
 11 . 4 	TOTAL RENT REVENUE              	- 2494560 
	OTHER REVENUE	
 [b]61 	Other Revenue	                            45 
 15 	TOTAL OTHER REVENUE                      12 [/b]
	TENNANT FEES	
- 9 . 8 	Late Fees	                          - 34639 
 132 . 4 	Administrative Fees	             - 1272 
 0 	Security Deposit Admin Fee	             - 3613 
 100 	Termination/Surrender Fees   	- 3800 

	RESIDENTAL RENT	
- 1 . 8 	Residential Rent-Stabilized	             - 1192865 
 26 . 3 	Residential Rent-DeStabilized	- 1193052 
 11 . 7 	TOTAL RESIDENTAL RENT	             - 2385917 
	RENT REVENUE	
 11 . 7 	Total Residental Rent	             - 2385917 
 3 	Commercial Rent	                          - 108643 
 11 . 4 	TOTAL RENT REVENUE              	- 2494560 
	OTHER REVENUE	
	TENNANT FEES	
- 9 . 8 	Late Fees	                          - 34639 
 132 . 4 	Administrative Fees	             - 1272 
 0 	Security Deposit Admin Fee	             - 3613 
 100 	Termination/Surrender Fees   	- 3800 

 For i =  555  To  1  Step - 1 
If Mid(Cells(i,  8 ),  1 ,  5 ) = "TOTAL" Then Rows(i +  1 ).Insert
Next i

     i =  5 
         Do 
 If Left(Cells(i,  8 ),  5 ) = "TOTAL" Then
            Rows(CStr(i +  1 ) + ":" + CStr(i +  1 )).Select
        Selection.Insert shift:=xlDown
 End If
    
     i = i +  1 
Loop Until i = iRows

 Sub testrrr()
Dim iRow%
Dim i%

 

iRows = ActiveCell.SpecialCells(xlLastCell).row
  
 
  Columns("M:M").Select
  Selection.Cut
  Columns("D:D").Select
  Selection.Insert Shift:=xlToRight

 
  For i =  1  To  6 
    If Left(Cells(i,  1 ),  4 ) = "ADD1" Then
      iLength = InStr(Cells(i,  1 ), "CSZ=")
      sAddr1 = Mid(Cells(i,  1 ),  7 , iLength -  7 )
      sCsz = Mid(Cells(i,  1 ), iLength +  5 )
      Rows(CStr(i) + ":" + CStr(i)).Select
        Selection.Delete Shift:=xlUp
      Exit For
    End If
  Next i
 
 
 
 
  Rows("1:2").Select
    Selection.Delete Shift:=xlUp
    
    
    
    
       Rows("2:2").Select
      Selection.Insert Shift:=xlDown
 

  
 
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Columns("A:A").ColumnWidth =  1 . 14 
    Columns("N:N").ColumnWidth =  1 . 14 
    
        'Set Row Heighth
    Rows("1:2").RowHeight =  12 #
    
    
    
  For i =  555  To  1  Step - 1 
If Mid(Cells(i,  8 ),  1 ,  5 ) = "TOTAL" Then Rows(i +  1 ).Insert
  Next i
   
       
    
        '---------------------
 
  i =  5 
      Do
                   
    Set x = Columns( 8 ).Find(What:="TOTAL OTHER REVENUE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x Is Nothing Then
    Set x = Columns( 8 ).Find(What:="OTHER REVENUE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x Is Nothing Then
        Rows(x.row).EntireRow.Delete
        End If
End If
          
     Set x1 = Columns( 8 ).Find(What:="TOTAL A&G PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x1 Is Nothing Then
    Set x1 = Columns( 8 ).Find(What:="A&G PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x1 Is Nothing Then
        Rows(x1.row).EntireRow.Delete
        End If
End If
                  
    Set x2 = Columns( 8 ).Find(What:="TOTAL A&G OTHER", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x2 Is Nothing Then
    Set x2 = Columns( 8 ).Find(What:="A&G OTHER", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x2 Is Nothing Then
        Rows(x2.row).EntireRow.Delete
        End If
End If

    Set x3 = Columns( 8 ).Find(What:="TOTAL MARKETING  PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x3 Is Nothing Then
    Set x3 = Columns( 8 ).Find(What:="MARKETING  PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x3 Is Nothing Then
        Rows(x3.row).EntireRow.Delete
        End If
End If

    Set x4 = Columns( 8 ).Find(What:="TOTAL MARKETING & ADVERTISING", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x4 Is Nothing Then
    Set x4 = Columns( 8 ).Find(What:="MARKETING & ADVERTISING", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x4 Is Nothing Then
        Rows(x4.row).EntireRow.Delete
        End If
End If

    Set x5 = Columns( 8 ).Find(What:="TOTAL UTILITIES", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x5 Is Nothing Then
    Set x5 = Columns( 8 ).Find(What:="UTILITIES", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x5 Is Nothing Then
        Rows(x5.row).EntireRow.Delete
        End If
End If

    Set x6 = Columns( 8 ).Find(What:="TOTAL OPERATIONS PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x6 Is Nothing Then
    Set x6 = Columns( 8 ).Find(What:="OPERATIONS PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x6 Is Nothing Then
        Rows(x6.row).EntireRow.Delete
        End If
End If

    Set x7 = Columns( 8 ).Find(What:="TOTAL MAINTENANCE PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x7 Is Nothing Then
    Set x7 = Columns( 8 ).Find(What:="MAINTENANCE PAYROLL", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x7 Is Nothing Then
        Rows(x7.row).EntireRow.Delete
        End If
End If

    Set x8 = Columns( 8 ).Find(What:="TOTAL REPAIRS AND MAINTENANCE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x8 Is Nothing Then
    Set x8 = Columns( 8 ).Find(What:="REPAIRS AND MAINTENANCE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x8 Is Nothing Then
        Rows(x8.row).EntireRow.Delete
        End If
End If

    Set x9 = Columns( 8 ).Find(What:="TOTAL CONTRACT MAINTENANCE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x9 Is Nothing Then
    Set x9 = Columns( 8 ).Find(What:="CONTRACT MAINTENANCE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x9 Is Nothing Then
        Rows(x9.row).EntireRow.Delete
        End If
End If

    Set x10 = Columns( 8 ).Find(What:="TOTAL OTHER INCOME", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x10 Is Nothing Then
    Set x10 = Columns( 8 ).Find(What:="TENNANT FEES", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x10 Is Nothing Then
        Rows(x10.row).EntireRow.Delete
        End If
End If

    Set x11 = Columns( 8 ).Find(What:="TOTAL TURNOVER COSTS", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x11 Is Nothing Then
    Set x11 = Columns( 8 ).Find(What:="TURNOVER COSTS", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x11 Is Nothing Then
        Rows(x11.row).EntireRow.Delete
        End If
End If

    Set x12 = Columns( 8 ).Find(What:="TOTAL NRP EXPENSE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x12 Is Nothing Then
    Set x12 = Columns( 8 ).Find(What:="NRP EXPENSE", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x12 Is Nothing Then
        Rows(x12.row).EntireRow.Delete
        End If
End If

    Set x13 = Columns( 8 ).Find(What:="TOTAL OTHER DEDUCTIONS", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If x13 Is Nothing Then
    Set x13 = Columns( 8 ).Find(What:="OTHER DEDUCTIONS", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
        If Not x13 Is Nothing Then
        Rows(x13.row).EntireRow.Delete
        End If
End If

  i = i +  1 
Loop Until i = iRows
 

 
    
    
    
 
 
  '-------------------------------------------------
   
i =  3 
Do
  
  
          If Left(Cells(i,  8 ),  5 ) = "TOTAL" Or _
             Left(Cells(i,  8 ),  22 ) = "GROSS OPERATING PROFIT" Then
             Range("B" & i & ":L" & i).Select
            
 
            
            
          With Selection.Interior  
            .ColorIndex =  19 
            .Pattern = xlSolid
          End With
            
           With Selection.Borders(xlEdgeTop)
               .LineStyle = xlContinuous      
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
           With Selection.Borders(xlEdgeBottom)
               .LineStyle = xlContinuous      
               .Weight = xlThin
               .ColorIndex = xlAutomatic
           End With
         End If
  
  '------------------------- added for 7 big groups
  
  
          If Left(Cells(i,  8 ),  21 ) = "TOTAL RESIDENTAL RENT" Or _
             Left(Cells(i,  8 ),  24 ) = "TOTAL OPERATING EXPENSES" Or _
             Left(Cells(i,  8 ),  16 ) = "TOTAL NET INCOME" Or _
             Left(Cells(i,  8 ),  16 ) = "TOTAL LABOR COST" Or _
             Left(Cells(i,  8 ),  32 ) = "TOTAL GARAGE DEPARTMENTAL PROFIT" Or _
             Left(Cells(i,  8 ),  30 ) = "TOTAL HEALTH CLUB DEPT. PROFIT" Or _
               Left(Cells(i,  8 ),  28 ) = "TOTAL GROSS OPERATION INCOME" Then
            Range("B" & i & ":L" & i).Select
            
            
            
            
      With Selection.Interior  
            .ColorIndex =  35 
            .Pattern = xlSolid
          End With
            
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
    End With
            
     End If
 
  
  '---------------------------------------  
   


          If Left(Cells(i,  8 ),  21 ) = "RESIDENTAL RENT" Or _
             Left(Cells(i,  8 ),  24 ) = "RENT REVENUE" Or _
             Left(Cells(i,  8 ),  16 ) = "OTHER REVENUE" Or _
             Left(Cells(i,  8 ),  16 ) = "TENNANT FEES" Or _
             Left(Cells(i,  8 ),  32 ) = "OPERATING EXPENSES" Or _
                Left(Cells(i,  8 ),  24 ) = "A&G PAYROLL" Or _
             Left(Cells(i,  8 ),  16 ) = "A&G OTHER" Or _
             Left(Cells(i,  8 ),  18 ) = "MARKETING  PAYROLL" Or _
             Left(Cells(i,  8 ),  32 ) = "MARKETING & ADVERTISING" Or _
             Left(Cells(i,  8 ),  30 ) = "UTILITIES" Or _
             Left(Cells(i,  8 ),  24 ) = "OPERATIONS PAYROLL" Or _
             Left(Cells(i,  8 ),  19 ) = "MAINTENANCE PAYROLL" Or _
             Left(Cells(i,  8 ),  23 ) = "REPAIRS AND MAINTENANCE" Or _
             Left(Cells(i,  8 ),  32 ) = "CONTRACT MAINTENANCE" Or _
             Left(Cells(i,  8 ),  30 ) = "TURNOVER COSTS" Or _
             Left(Cells(i,  8 ),  32 ) = "RENOVATION" Or _
             Left(Cells(i,  8 ),  30 ) = "NRP EXPENSE" Or _
             Left(Cells(i,  8 ),  30 ) = "OTHER DEDUCTIONS" Then
            Range("B" & i & ":L" & i).Select
            
 
            
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
    End With
            
     End If
  
 
  
  i = i +  1 
Loop Until i = iRows



 


  '-----------------------------------------  
 
   
  i =  6 
Do
     


     If Cells(i,  3 ) <  0  Or _
        Cells(i,  3 ) >  0  Then
        Cells(i,  3 ) = - 1  * Cells(i,  3 )
    End If

     If Cells(i,  4 ) <  0  Or _
        Cells(i,  4 ) >  0  Then
        Cells(i,  4 ) = - 1  * Cells(i,  4 )
    End If

     If Cells(i,  6 ) <  0  Or _
        Cells(i,  6 ) >  0  Then
        Cells(i,  6 ) = - 1  * Cells(i,  6 )
    End If


 


    
     If Cells(i,  11 ) <  0  Or _
        Cells(i,  11 ) >  0  Then
        Cells(i,  11 ) = - 1  * Cells(i,  11 )
    End If

    
 

  
     If Cells(i,  10 ) <  0  Or _
        Cells(i,  10 ) >  0  Then
        Cells(i,  10 ) = - 1  * Cells(i,  10 )
    End If
  
     If Cells(i,  9 ) <  0  Or _
        Cells(i,  9 ) >  0  Then
        Cells(i,  9 ) = - 1  * Cells(i,  9 )
    End If
  
  
  
  
  i = i +  1 
 
Loop Until Left(Cells(i,  8 ),  18 ) = "OPERATING EXPENSES"

   


 
 
   
   
   

 'delete extra rows
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
 
 Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    
     Columns("B:B").Select
    Selection.Insert Shift:=xlToLeft
    
    
    
 
  'set data font size & row heighth
  Range("A1:N" & iRows +  4 ).Select
    Selection.Font.Size =  6 
  Rows("3:" & iRows).Select
    Selection.RowHeight =  10 . 5 


  Columns("B:B").ColumnWidth =  5   '12
  Columns("C:C").ColumnWidth =  10   '30
  'Columns("C:C").HorizontalAlignment = xlLeft
  Columns("D:M").ColumnWidth =  10 
  Columns("N:N").ColumnWidth =  10 

  Columns("D:F").ColumnWidth =  10   '12
  Columns("G:G").ColumnWidth =  25 
  Columns("G:G").HorizontalAlignment = xlCenter
  Columns("H:K").ColumnWidth =  10                      
        Columns("L:M").ColumnWidth =  4 
          'Format date cells in heading
  Range("I2:M2").Select
  Selection.NumberFormat = "mmm-yy"

  
        'Center and shade heading box
    Range("A1:N2").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    With Selection.Interior
        .ColorIndex =  36 
        .Pattern = xlSolid
    End With
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

End Sub

  For i =  1  To  6 
    If Left(Cells(i,  1 ),  4 ) = "ADD1" Then
      iLength = InStr(Cells(i,  1 ), "CSZ=")
      sAddr1 = Mid(Cells(i,  1 ),  7 , iLength -  7 )
      sCsz = Mid(Cells(i,  1 ), iLength +  5 )
      Rows(CStr(i) + ":" + CStr(i)).Select
        Selection.Delete Shift:=xlUp
      Exit For
    End If
  Next i

For i = iRows To  1  Step - 1 
If Mid(Cells(i,  8 ),  1 ,  5 ) = "TOTAL" Then Rows(i +  1 ).Insert
Next
iRows = ActiveCell.SpecialCells(xlLastCell).row