SOLVED

hiding rows based on condition

Iron Contributor

is it possible to hide rows in excel 365 based on the content of a specific cell?

15 Replies

@ajl_ahmed 

Yes; it would require VBA code.

Can you provide more detailed information?

  • What is the specific cell?
  • Will the value of this cell be changed by the user, or does it contain a formula?
  • What are the criteria for hiding rows?
  • Which row or rows do you want to hide?
Can I send excel file here?

@ajl_ahmed 

Do you see an area below the reply box with "Drag and drop here or browse file to attach"? If so, drag your workbook into that area.

Otherwise, you can upload the workbook to a cloud service such as OneDrive, Google Drive or Dropbox, share it and post a link to the shared file in your reply.

@HansVogelaar 

I have three pages in the “Sheet1” of the "test3" excel file, I need to create four buttons to: 

  • Button 1: display and print Page 1 only and hide blanks cells "if found" based on the content of cell “A” , (if it includes a name or not in each row)
  • Button 2: display and print Page 2 only and hide blanks cells "if found" based on the content of cell “A” , (if it includes a name or notin each row)
  • Button 3 display and print Page 3 only and hide blanks cells "if found" based on the content of cell “A” , (if it includes a name or notin each row)
  • Button 4: display all pages and hide blanks cells "if found" based on the content of cell “A” , (if it includes a name or notin each row)

@ajl_ahmed 

Where do you want the buttons? We can't place them next to one of the three pages because they might be hidden?

ca you put it at the first row starting from C1 before the row of student name and degree?

@ajl_ahmed 

But buttons 2 and 3 will hide page 1...

is it possible to put buttons in new page (page 4) of sheet1?
choose suitable places for them

@ajl_ahmed 

See the attached workbook (now a macro-enabled workbook, so you'll have to allow macros).

The buttons are below page 3.

Many thanks, dear for your efforts.
Is it possible to split the displaying process from the printing process.? I mean, I click on the button first then I do the order of printing.
best response confirmed by ajl_ahmed (Iron Contributor)
Solution

@ajl_ahmed 

Sure.

I appreciate too much your efforts dear. I will try this code and If I have a problem or face another problem, I will contact you. Thanks again.
Best wishes

@HansVogelaar 

Good Evening 

Sorry for disruption

I have applied the code with some modifications on the number only. However, I have faced some problems in specifying the page print area. I decided to send you the original file "AA enabled macro + Hidden row"  and write here some notes as below:

In the "AA enabled macro + Hidden row", original file:

  1. I set rows 1 to 11 to repeat at the top when printing each page.
  2. Before applying the macro which hides blank rows, the Page 1 border is (A12:AG46), the Page 2 border is (A47:AG81), the Page 3 border is (A82:AG119)
  3. When applying the hiding macro, I need the last three rows(A117:AG119) to appear at the end of each page when clicking on buttons 1,2,3, and All. In addition when doing the printing process. see the attached picture which shows the problem is.  
  4. I need the process of hiding rows based on the condition of existing text on yellow cells. 
  5. As I said before, I have three pages in the “Sheet1” of the "test3" excel file, I need to create four buttons to: 
  • Button 1: displays  Page 1 only and hides blanks cells "if found" based on the content of cell “B-Yellow Cell”, (if it includes a name or not in each row)
  • Button 2: displays  Page 2 only and hides blanks cells "if found" based on the content of cell “B-Yellow Cell”, (if it includes a name or not in each row)
  • Button 3 displays Page 3 only and hides blanks cells "if found" based on the content of cell “B-Yellow Cell”, (if it includes a name or not in each row)
  • Button 4: displays all pages and hide blanks cells "if found" based on the content of cell “B-Yellow Cell”, (if it includes a name or not in each row)
  • Button 5: Print the displayed page

Also, I sent you "test5" file after applying the hidden rows code and a picture shows the problem. 

 

Many thanks in advance 

Regards

@ajl_ahmed 

Sorry, that's too complicated for me.

1 best response

Accepted Solutions
best response confirmed by ajl_ahmed (Iron Contributor)
Solution