I love Quicken, but it certainly has its annoyances. I have a certain memorized report in Quicken with categories down the side and months going across the top. Quicken offers the opportunity to print this report, but of course, I always just use the Copy command to copy the report to the clipboard and then use Edit > Paste in Excel to copy the report to the clipboard. This feature is much faster than the older (and still available) print to a .prn file option.

Quicken allows you to use categories and sub-categories to classify your expenses. In the report at left, the automotive expenses are further broken out by insurance, gasoline, repairs and license plates. Perhaps I did a poor job of setting up my categories, but I find that I have some categories where I want to see the subcategory detail and other categories where I would prefer to just see the category total. I would also like to be able to sort this report in Excel. It would be useful to sort it by total expense, then sort it back by category. Sure, I could use undo, but I would like categories that are alphabetic in nature. In short, I don't care for the outline format used by Quicken. Long time readers will remember my disdain for the outline format used by pivot tables in tip004. We have the same situation here. If the Quicken report is merely an intermediate step and you want to be able to sort by category, the outline format is horrible. After sorting by totals and then by category, the Auto:Insurance category will be mis-sorted into the "I" section of the report. For the categories where I keep only the total, they will be incorrectly sorted into the "T" section of the report.

At the right, is my improved version of the report. By assigning Collapse and Fill to hot keys, I was able to make these changes with just a few keystrokes. It is now easy to sort the report, knowing that the report can return to it's original sequence by sorting the category. If you are new to macros, review How to copy a macro from the web into Excel Once you copy the macro, you can assign a hot key by following these steps: From the Tools Menu, Choose Macros then Macro Highlight the Fill macro. Click Options. In the Shortcut field, enter any letter. I use f for Fill. Click OK Highlight the collapse macro. Click Options. Pick a letter for a shortcut, but stay away from c, as Ctrl+c is the common shortcut for Edit>Copy. Click OK Close the macro dialog with Cancel.

Option Explicit
Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running
Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows
Dim Counter As Integer 'Counter to ensure searches are relative to activecell
Dim TempString, TempTest As String 'Strings that are used as temporary holders to compare
Dim CategoryName As String 'Name of the category currently in
Sub Collapse()    
'To collapse the rows, run this macro    
Flag = False    
Flag2 = False    
Counter = -1        
'Continue looping until HeaderRow is found    
Do Until Flag = True        
    Counter = Counter + 1        
    Call FindHeader    
Loop        

'Create CategoryName    CategoryName = Left(CategoryName, Len(CategoryName) - 1)    
CategoryName = Trim(CategoryName)        
'Assign HeaderRow    
HeaderRow = ActiveCell.Row - Counter    
Counter = 0        
'Continue looping until TotalRow is found    Do Until Flag2 = True        
    Counter = Counter + 1        
    Call FindTotal    
  Loop

'Assign TotalRow    TotalRow = ActiveCell.Row + Counter        
' Replace "TOTAL Category" with "Category"    
Cells(TotalRow, 1).Value = "   " & CategoryName        
'Deleting sub-category rows    
Rows(HeaderRow & ":" & TotalRow - 1).Delete Shift:=xlUp
End Sub

Sub Fill()    
'To add the CategoryName to each of the types, run this macro    
Dim i As Integer  Flag = False    
Flag2 = False    
Counter = -1        
'Continue looping until HeaderRow is found    
Do Until Flag = True        
  Counter = Counter + 1        
  Call FindHeader    
Loop        
'Create CategoryName    
CategoryName = Left(CategoryName, Len(CategoryName) - 1)    
CategoryName = Trim(CategoryName)        
'Assign HeaderRow    HeaderRow = ActiveCell.Row - Counter    
Counter = 0        
'Continue looping until TotalRow is found    
Do Until Flag2 = True        
  Counter = Counter + 1      
  Call FindTotal    
Loop        

'Assign TotalRow    
TotalRow = ActiveCell.Row + Counter        

'Adding CategoryName and colon to the start of each sub category    
For i = HeaderRow + 1 To TotalRow - 2        
  TempString = Trim(Cells(i, 1).Value)     
  Cells(i, 1).Value = "   " & CategoryName & ": " & TempString    
Next i        

'Deleting Header and Total Row    
Rows(TotalRow & ":" & TotalRow - 1).Delete Shift:=xlUp    
Rows(HeaderRow).Delete Shift:=xlUp
End Sub

Sub FindHeader()    
Dim i As Integer    TempString = Cells(ActiveCell.Row - Counter, 1).Value        
'Checking to see if in first row    
If (ActiveCell.Row - Counter) = 1 Then      
  MsgBox "You are not in a collapsable row"        
End If        
'Checking to see if in a valid row    
'If it reaches a TOTAL before reaching a colon, then error    
If Left(Trim(TempString), 5) = "TOTAL" Then      
  MsgBox "You are not in a collapsable row"        
End If     
'Checking the String for a colon    
For i = 1 To Len(TempString)        
  TempTest = Mid(TempString, i, 1)          If TempTest = ":" Then            
     CategoryName = TempString
     Flag = True            
     Exit For        
  End If    
Next i    
End Sub

Sub FindTotal()
Dim i As Integer    
'Finding the TOTAL Row    
TempString = Cells(ActiveCell.Row + Counter, 1).Value    
TempString = Trim(TempString)    
If TempString = "TOTAL " & CategoryName Then
  Flag2 = True    
End If
End Sub

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.