Hide Row Macro (2000)

Topic

New Reply

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 D2:D1500 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

Reply | Quote

Replies

Set the worksheet name and column as appropriate:

Sub hideblanks()
Worksheets(“Sheet1”).Columns(“D:D”).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

Reply | Quote

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.

Reply | Quote

Stats, see if this does it for you:

Sub HideNullStringCellRows()
Dim rngCell As Range
For Each rngCell In Worksheets(“Sheet1”).Columns(“D: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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Similar code as posted:
Use “entireColumn” rather than “entireRow”

Steve

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

Legare,
I tried it, but it didn’t work. Care to try?
Jeff

Reply | Quote

There were a couple of problems. First, when you copied the code from the Lounge you pasted directly into Excel. That causes the formatting to get all messed up, and the code would not compile. When you copy code from the Lounge, you have to paste it into Word first, then copy it from there and paste it into Excel. The second problem is that your worksheet was not exactly as I expecte it to be. You have some columns past the end of the daily data and you are filling in the “N/A” for the weekends before you get there. Both of those would cause the original macro to not work. Try the attached copy.

Reply | Quote

Thanks, Legare. Works perfectly!

Reply | Quote