I would need two macros for one of my projects. I did search the forum, but I couldn't find anything what would suit my needs.
The first macro should hide those rows which would contain 0 (zero) in a specific column (in my workbook it's E). The secon macro should unhide the hidden rows.
Sound so easy ... but sadly I can't do it.
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
I have a problem with hiding and unhiding columns. Is there a way wherein I can automatically hide the row(s) if the cell = "N/A" and will unhide if it's not equal to "NA". see the attachment Im using excel 2003 btw. You can see in Sheet2 to that it contains the table for the student grades. "N/A" means that they are not enrolled on that subject. While in sheet1, What I want to happen is that you will select a student name and below, it will automatically display the grades of the student on their enrolled subjects only. So, those fields with "N/A" will hide and when I update the value of N/A it will unhide.
View 5 Replies View RelatedFor data validation lists B27 to B30, if any of them is equal to "EBS via ULL" then unhide rows 31 to 42.
If any of the data validation list not equal to "EBS via ULL" then unhide row 44 and hide rows 31 to 42.
If "EBS via ULL" and any other product in the lists is selected, unhide rows 31 to 44.
If validation lists B27 to B30 are all equal to "Select Product..", then hide rows 31 to 44.
I would like to use the worksheets_change event.
I am trying to Hide and Unhide several rows depending on if several checkboxes are marked or not.
[Code] .....
This is the code I am using, which is effective in hiding and unhiding the rows. However, the issue I am having is: I want Row 20 to stay visible if either one of the checkboxes is marked. This string of code runs the "hide" portion if either box is unchecked.
I am trying to work out a macro to hide and unhide rows in worksheet 6 based on a cell input (cell I6) in worksheet 1 based on the following:
>> If I6 in Worksheet 1 = 1 or 5 or 6 then Hide Rows 19 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 2 then Hide Rows 21 to 24 in Worksheet 6
>> If I6 in Worksheet 1 = 3 or 4 or 7 then Hide Rows 17 to 20 in Worksheet 6
>> If I6 in Worksheet 1 = 8 or 9 then Hide Rows 17 to 20 and Rows 23 to 24 in Worksheet 6
I have groups of data in A5:AW200 with various blank rows in between, and would like to be able to automatically hide and unhide (toggle) the blank rows, but only for those rows where cells in column A are blank.
I'd also like to hide those same groups of blank rows on Sheets 2-5, which are laid out exactly the same as Sheet 1, but since they contain cell references to and are mirrors of Sheet 1, certain rows on those sheets may appear blank, but in fact are not. (I should probably have separate buttons on each sheet, but not sure). I know Sheet 1 can be used to test for conditions (blanks rows) and translate the results to Sheets 2-5, but I'm not sure how to do it.
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
I have a spreadsheet that i would like to hide all columns from B:M and to select the column/s I want to unhide.
I would like VBA cose to do this or to set up a combo box that will allow me to hide/unhide specific columns....
My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.
If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows
I would like to be able to hide the tab i am in and unhide another tab at the click of the button,
I have tried recording the macro myself and it works up to a point,
I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)
Is it possible to set up a hyperlink for a cell, when clicking on it, hides/unhides certain rows below that cell. For example, having a full employee roster, each name having 15 rows underneath. By making the Name cell hyperlink, and by clicking on it, I would like to only hide every 4, 5, & 7th line underneath the hyperlinked name. A full list could have 150 employees on one sheet.
View 9 Replies View RelatedI want to hide and unhide tool / macros menu so that users don't run the macros in the workbooks.
I'm trying to hide all columns which have the word "hide" in row 6. I have done a similar thing whereby I hide all rows which have the word "hide" in column 3 using the following
Sub HURows()
BeginRow = 9
EndRow = 40
ChkCol = 3
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub
Alas changing the number and every Col for Row and vice versa doesn't work!! Really I only need to search colums G to U inclusive. The code must also unhide colums if the values in the cells of row 6 change to anything other than "hide".
I have a spreadsheet where I have a simple formula. The formula is IF($A2=$B$2,1,0), and then I drag down to the bottom of the spreadsheet, so whenever it matches it shows a 1.
Now, what I need to do using VBA is hide all the rows that do not have 1's on them. I know how to start the VBA, but not how to finish
Code:
Dim LR as Long
LR = Range("A2").End(xlDown).Row
Because that will allow me to only select the row field that I want to do the Macro on. So, how do I go about hiding rows that only have 0's in them.
I am using a pivot table in excel 2010. 15 columns (fixed) and plenty of dive downs for rows.
I am trying to 'hide' all of the rows containing values less than 10. If it is easier we can start with hiding values of zero and go from there.
It is key to hide the rows as within the dive downs the rows within the pivot are to many to sort through. If there is a way to do this using the grand total columns for each row that would work as well. Just to be clear I need to hide the entire row not just report the empty cells as zeros. If any further detail is needed feel free to ask.
For example: If value of column O "Grand Total" = 0 'hide rows with value 0' ELSE 'display rows with value greater than 0'
I have five worksheets hidden in my workbook. All of these worksheets have either "Yes" or "No" written in cell C4. (3-yes, 2-no)
I also have a search bar in the form of a textbox and a command button.
I want to be able to type "No" in the textbox, click the command button and have the pages with "No" in cell C4 to unhide.
Some screenshots if necessary:
Sheet1 (yes): Gyazo - ce85d71bc03cd2ceaa1030e22445800f.png
Sheet2 (no): Gyazo - 65f876d6e7d749bd1ca25ad957fbda99.png
Sheet3 (yes): Gyazo - 0cb442f1d34e7d744884f73e1afe2646.png
Sheet4 (yes): Gyazo - 6f0ce150322208fd93dc5fc6f4f66481.png
Sheet5 (no): Gyazo - 772f48f8a82e53713794e6655fe56fca.png
master: Gyazo - 1b71470f3de47cef347050588bca819c.png
I have been attempting to cobble together code from different forums for the past several hours to make a macro to unhide a specific number of worksheets based on a cell value, and have failed miserably thus far. Here are the specifics of what I have going on:
Microsoft Excel 2010
Workbook Name: TASERR Model - 06-26-13.xlsm
In this workbook, I have a worksheet called "Input". In cell C21 of that worksheet, workbook users will fill in a number between 1 and 50 (I have the value for that cell limited to those numbers through data validation). Based on the value in that cell (C21), I want to make a macro to unhide that specific number of hidden worksheets. I have 50 hidden worksheets which are named "Route (1)", "Route (2)", "Route (3)", etc.. If the value in cell C21 is 7, I want the worksheets named "Route (1)", "Route (2)", "Route (3)", "Route (4)", "Route (5)", "Route (6)", and "Route (7)" to unhide. If the value in cell C21 is 3, I want the worksheets named "Route (1)", "Route (2)", and "Route (3)" to unhide. I'm sure y'all can see the functionality I'm going for at this point.
I'm looking to see if it's possible for me to leave information in specific cells hidden unless a checkbox is marked off. I do not need/want the entire column or row hidden (which I've found plenty of tutorials on via google)
I'll try to provide a basic example:
ie: Column A1: checkbox;;;Column B1: Dairy;;;Column C1: milk;;;Column D1: slim milk
Column A2: checkbox;;;Column B2: Poultry;;;Column C2: beef;;;Column D2: hamburgers;;;Column E2: bubba burgers (<--not a fan of them)
(the values in row C and D will never change - there are some instances where there are values in column E)
I need the checkboxes in column A and the information in column B visible at all times but C and D (and E) visible only if the checkbox is checked.
Here is an example code I tried creating myself (with .entirerow.hidden and .entirecolumn.hidden codes I found online). Obviously it didn't work lol, but I was wondering if there is a way to only get these specific cells to hide rather than an entire row/column.
Sub CheckBox1_Click()
If CheckBox1_Click = True ThenRange(C26, C27, D26, D27).Cells.Hidden = False ElseRange(C26, C27, D26, D27).Cells.Hidden = True End IfEnd Sub
I need a code (and where to put it) to open my application excel with only the userform so, with the workbook hidden.
Application.Visible=False doesn't work because it close all excels and moreover I can see the workbook for 1 or 2 seconds before hiding.
Check the attached sample file
a. actual data in columns is approx 150 items (that is why i need to hide/unhide columns)
b. there are about 120 persons whom above items are issued depending on expiry of life of item i.e. issue date plus number of months mentioned in row 3. for example if coat was issued to John on 5th Jan then it's life expires on 5th May and so on.
Now what i want
1. by clicking hide checkbox only that specific column should hide or if i click Hide All check box all column in range should hide.
2. as i enter issue date expiry of that item should automatically update on corresponding month sheet i.e for above example 5th May should automatically get entered in sheet May.
I have entered sample data manually in months sheet.
aJITCBP.xlsm
I have this model I created where I have two tabs. One tab is an input tab using validation and drop down menus and the other is a display tab. I simply want to hide certain QTRs based on the value of one of the drop down menu results. I tried writing the VBA code below but am a novice when it comes to code. Can somebody please help me fix the below code so that it works properly.
Public Sub hide()
If Worksheets("input").Range("b14") = "Q1" Then
Worksheets("Group P&L").Columns(c, d, e, h, i, j, m, n, o, r, s, t, w, x, y).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q2" Then
Worksheets("Group P&L").Columns(c, d, h, i, m, n, r, s, w, x).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q3" Then
Worksheets("Group P&L").Columns(c, h, m, r, w).Hidden = True
ElseIf Worksheets("input").Range("b14") = "Q4" Then
Worksheets("Group P&L").Columns.Hidden = False
End If
End Sub
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell
I have a workbook with the following sheets i do not want to hide called "Detailed Template", "INTERFACES", "STATUS".
I then have a number of worksheets which have their names as cell addresses i.e. "E4", "E5", "D4", "F4", "G7" and so on.
I would like to hide all worksheets (except the 3 mentioned at the top [which are sheets 1-3]) and any which contain say the value of '4'. This value is defined by a cell in the "INTERFACES" worksheet say 'A1' for example.
I want the macro to be able to automatically do this for which ever value is in 'A1'. So if it were to change to '7' it would hide every sheet that doesn't contain '7' in the name.
I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.
View 2 Replies View RelatedI am building a real estate model. What I am trying to do is the following:
If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.
a bit of code so that when I open a sheet (which takes data from another sheet) it hides rows which contain a zero in a certain cell?
View 10 Replies View RelatedThe attached sheet takes information from another sheet in my work book (I am only providing the sheet I was to work with). I want all rows where the value in F is 0 to hide automatically.
Attached File : Book2.xlsx
I have had great success using this code to hide and unhide unused space, but for some reason this morning it broke. Most likely it is my error so I am looking for another solution to resolve the issue. What i would like to be able to do it hide an entire row based on a specific cell being blank. maybe that is what this code is supposed to do but like i said it broke this morning. the cell in ref in the code is unchanged, but i did add formulas to some other cells in the same row. this is why i am looking for a way to do the same thing but based on a specific cell being blank vs what is occuring now.
View 4 Replies View RelatedI have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this?
As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.