Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hide Row Macro (2000)

    I am using MS Query to pull off some information from our SQL database to create a report and without going into too much explanation, there are items popping up that should not be there (Sales orders that were supposed to be closed months ago etc). What I have been trying to do with a macro, with no success, is hide a row if the value of D21500 is "" after the MS Query has run and unhide them when closing the sheet. I am very new to macros and I was able to hide one row at a time with a macro but it stopped as soon as there was data in a "D" cell and it took a long time. I have no idea how to unhide when I'm done.
    Any help you folks could offer would be appreciated.
    Stats

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hide Row Macro (2000)

    Set the worksheet name and column as appropriate:

    Sub hideblanks()
    Worksheets("Sheet1").Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Hi dden = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Thanks for your quick reply.
    I'm sorry, I forgot to mention that the cells in Col D contain a formula that returns the value "" if some conditions aren't met. These conditions determine if the data retrieved is supposed to be there. Since the cells are not "blank" the macro is not hiding them.
    It worked perfectly on rows that had no data though.
    Thanks for your help.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Hide Row Macro (2000)

    Stats, see if this does it for you:

    Sub HideNullStringCellRows()
    Dim rngCell As Range
    For Each rngCell In Worksheets("Sheet1").Columns("D").SpecialCells(xlCellTypeFormulas, 2)
    If rngCell.Value = "" Then rngCell.EntireRow.Hidden = True
    Next rngCell
    End Sub

    This version however doesn't hide cells which are blank and contain no formula at all. Let me know if you also intend to hide those.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Thank You very much John!!
    It works great, I don't need blank or empty cells hidden, it was just the gaping holes in the report I needed cleaned up and this works fine.
    Thanks again for taking the time to help.
    Stats

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    John,
    I have a similar problem, only mine is with columns. I would like to hide columns wherein there are data entries in row numbers, say, 3-10 (rows 1 and 2 may or may not contain data).Do you have any suggestions?
    Thanks,
    Jeff

  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

    Re: Hide Row Macro (2000)

    Similar code as posted:
    Use "entireColumn" rather than "entireRow"

    Steve

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    I don't understand your question. What column or columns do you want to hide when there is or is not data in what column or columns in rows 3-10?
    Legare Coleman

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Legare,
    I would like to be able to hide those columns where there is data entered in rows, say, 3-10.
    Jeff

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Sorry, I still don't understand. Which columns do you want to hide? Do you want to hide them if there is data in any column of rows 3-10, or just if there is data in one column or a few columns? Are you trying to hide just the columns on the row where there is data, which is not possible? Lets say we get to row 4, and there is data in columns C, E, and F, what columns do you want to hide? Then, lets say we get to row 5 and there is data in a column that has already been hidden, what do you want to do?
    Legare Coleman

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Not your fault-I didn't make myself clear. In my worksheet, each columns B-AE (or AF) represent a day of a month. Column A is reserved for a description of a product I price every day. There are 48 of these. Row 1 is reserved for the date in "mm/dd" format, and Row 2 for the day of the week in "DDDD" format, referencing the respective cell's counterpart in Row 1.

    I would like to input daily prices for these products in cells B3:AE51, and hide the columns where I have input the daily prices. For example, since today is the 7th, I will have already entered data in columns B-E, corresponding to Tuesday-Friday of last week (no prices for Saturdays and Sundays). Therefore, I would like to hide columns B-E.

    I hope this makes more sense.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    OK, I think we are close to being able to give you what you want. Just a couple of more questions.

    1- Since there are no prices for Sat and Sun, do you want them included in the columns or would you like to eliminate them? How about holidays?

    2- If you still want to include weekends and holidays in the columns, do you ever want to hide those columns? For instance, when you run the macro today, it would hide columns B through E. Columns F and G would be for Sat and Sun. Should those also be hidden? Same question for holidays?

    3- Is there always a price for every product? In other words, if I find the last column with a price entered in row 3, can I just hide the columns B through that column, or do I have to look at all of the rows to find the last price entered?
    Legare Coleman

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Legare,
    1. I had planned in a formula to enter "N/A" for the weekend days. I would have to do it manually for holidays;
    2. Yes-I had envisioned if there was either data or text in the cells in the columns (except, of course, rows 1 and 2 where the date and day show up), the columns would be hidden;
    3. There will be prices for every product, every day (except, of course, Saturdays, Sundays & holidays).
    Jeff

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    OK, then the macro below should do what you asked:

    <pre>Public Sub HideCols()
    Dim iLastCol As Integer
    iLastCol = Worksheets("Sheet1").Range("IV3").End(xlToLeft).Co lumn - 1
    Worksheets("Sheet1").Range("B1:" & _
    Range("A1").Offset(0, iLastCol).Address).EntireColumn.Hidden = True
    End Sub
    </pre>

    Legare Coleman

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Hide Row Macro (2000)

    Legare,
    I tried it, but it didn't work. Care to try?
    Jeff
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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