Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro in Excel 2007

    Accounting is downloading a file from Quickbooks - it's composed of numbers with totals for each category. We want to hide rows with the numbers and keep the rows which show the totals for the categories. The totals contain a formula for 'Round'. I thought if I selected the column and then checked each row in the column for the formula, I could ask it to hide the rows without the formula. No?

    I also want to put a pop-up box into the formula so the user would input the range of rows to be searched.

    Can I search a cell based on part of a formula?

    Thanks

  2. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Have you looked at using AutoFilter to show only the rows that contain the formula?
    PJ in FL

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by hawkfeather View Post
    Can I search a cell based on part of a formula?
    You can - but its' awkward - can you expand on why you want to do this?

    You might also want to take a look at Outlining in Excel You'll find the Outline group on the data ribbon. Excel can automatically outline the spreadsheet or you can apply it yourself.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The AutoFilter only partially helps - it highlights the rows with the formula - I need to then write code that would eliminate the other rows.

  5. #5
    Lounger
    Join Date
    Oct 2003
    Location
    Calgary, Alberta, Canada
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We create a recon in Quickbooks that needs to be sent to the client showing the original estimate, costs paid to date and outstanding balance. Each category has its own list and subtotal. the client is shown only the subtotals. I need to hide everything else when I pdf the document. However no document ever has the same categories and sub-categories. Because of that I can't apply the outlining feature.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Use Outline

    I re-iterate the comment about Outlining
    This will group rows and allow you to collapse detail rows and thus showing rows with formulae only.
    G
    Attached Images Attached Images

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If autofilter can be used to show the desired rows, it can also be used to show the converse, hiding those rows and displaying the rows to get rid of. Once you have the rows you want to delete, you can select all of those rows and delete, then unfilter...

    Steve

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Lightbulb I'd use VBA

    Hawkfeather,

    1. Do the sub total rows have any identifying information like "SubTotal Category1"?
    2. Does the Quicken dump have a hard coded value for the subtotal or is it a formula?

    If the answer to 1. is Yes and the answer to to 2 is hard coded it would be very simple to write a VBA routine to delete all the rows that are not identified as a subtotal. If the answer to 2. is formula you would first have to convert all formulas to values before doing the deleting.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    A lttle cheap nasty vba starter

    Hi
    Convert formulae to values first otherwise you will look at a lot of junk.
    Something else to explore could be vba

    ActiveCell.hasFormula

    In the silly routine below you would need to make a better job of controling the loop.

    Sub deleteDetailRows()
    Range("A2").Select
    For x = 1 To 5
    If (InStr(ActiveCell.Value, "sub")) = False Then
    ActiveCell.EntireRow.Delete
    End If
    Next
    End Sub
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •