Macro to Hide lines without data in a certain range
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Macro to Hide lines without data in a certain range

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a spreadsheet with a looong list of part numbers in column A. At the end of the day, I need to print only the lines that have data in the area of the spreadsheet where I enter quantities, because the entire parts list is much too long to print. Usually there are only ten rows with data in them to print, so I have to go through a lot of work to manually hide them. Anyone know of a way that I can write a macro to go through a range and hide all the rows that have no data in them? I could name the range with the quantities in it "quantities" and have the macro run row by row. The other good thing is that to the right of the quantities range, I have a column with a count going for each row. I could also have my macro go through that column, and hide any rows where the cell value is 0. Does anyone know how I can program this? I know programming, but for me the syntax is always alien...I know I can run a macro recorder to get the syntax for hiding a row, but how do I write the if statement and how do I tell the macro to go to the last row in teh spreadsheet and then stop??

    Thanks!

    Claudette

  2. #2
    Guest

    Default

    Example :-

    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Claudette


    I hate loops, but this should do if you want a quick answer. If not try here:
    http://www.ozgrid.com/VBA/VBACode.htm


    Sub HideIfEmpty()
    Dim rRows As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each rRows In Range(Selection.Cells(1, 1) _
    , Selection.Cells(65536, 1).End(xlUp))
    rRows.EntireRow.Hidden = (rRows = 0)
    Next
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub


    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-12 07:44 ]

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 07:42, Anonymous wrote:
    Example :-

    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
    I don't understand what this does, or where I should put it?

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Claudette,

    To use any or all of the above code:


    Open Excel.
    Push Alt+F11 to open the VBE (Visual Basic Editor).
    Go to Insert>Module.
    Copy the code and paste it in the new module.
    Push Alt+Q to return to Excels normal view.
    Push Alt+F8 and then select the macro name and click Run. Or select Options and assign a shortcut key.


  6. #6
    Guest

    Default

    What object in the photograph are you referring to?

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When I ran the macro, the program gave me this error:

    Runtime error 1004:
    Application-defined or object-defined error



    And when I clicked on "Debug" it highlighted the part of hte text that says"

    For Each rRows In Range(Selection.Cells(1, 1) _
    , Selection.Cells(65536, 1).End(xlUp))


    Help!!



  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Boston
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    WOW. this worked. neat. THANKS!!


    On 2002-03-12 07:42, Anonymous wrote:
    Example :-

    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com