Toggling (hiding/unhiding) Blank Rows Across Sheets
May 5, 2006
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.
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.
For 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.
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
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.
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
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.
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 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.
I currently have the code below for a button that will unhide last 175 rows in my worksheet. I now would like to have it hide the rows that are blank in in the range (C7:BL206) looking from the bottom up. For example, if there were values in any of the cells (Columns C up to BL only) in row 190, the macro would only hide rows 191 thru 206.
Private Sub CommandButton2_Click() ActiveSheet.Unprotect Password:="" Range("A32:A206").EntireRow.Hidden = False ActiveSheet.Protect Password:="" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub
I'm trying to run this macro and I keep getting the "Run-time error '1004' Application-defined or object-defined error"
I am just trying to hide the rows that do not contain anything in them with this
x = 2 Sheets("sheet2").select If Cells(x,1).value = "" Then Rows("x:x").select Selection.EntireRow.Hidden = True Else x = x +1
The line that is giving me a problem is "Rows("x:x").select". but when i change x to an actual number, it works. I cannot have a definitive number because where there is data will vary every time this is ran.
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.
My database has what I call the 'cover page'. On the sheet is a command button that will take the user to the next page. Basically what I want to do is upon opening the workbook, the user is taken to the cover page, and all the rest of the sheets remain hidden until they click a command button to 'enter' the workbook. P.S. I have some sheets that need to remain hidden though...
Of course the other way is just to have a userform that opens when the book is opened to do the same thing. Where would I put the code for the userform, in ThisWorkbook or a separate module?
Regarding the age old problem of trying to ensure macros are enabled in a workbook, using the process of hiding all sheets bar one:
Automatically Close Workbook If Macros Are Disabled
Reafid inserts some excellent code so that worksheets are hidden BOTH before closing AND on a normal Save event (I have also attached Reafid's zip file with the workbook that includes his/her code).
Having done much searching it seems to be the best solution out there, however I can still break it fairly easily and I'm worndering if anybody can stop me doing the following to break it:
Open the file in its restricted state (i.e. only Warning tab shown, all other sheets very hidden) and NOT enabling macros on opening. I'm therefore at the position Reafid intended; macros are disabled so all sheets are very hidden. Then to break it, in my personal macro workbook I have a simple macro that unhides all worksheets in ThisWorkbook, I run that personal macro and everything in Reafid's file is unhidden.
Reafid's file therefore has been opened with macros DISABLED and now all worksheets are visible.
I have the below VBA code associated with a sheet called "Select View". When the workbook opens there is another code that displays only "Select View", all the other sheets are hidden. In "Select View" there is a data validation list. Based on the selection the user gets to see only certain sheets. For example, if the user selects "Region 1" they see only the sheets "Region 1", "Panama City" and "Pensacola".
As you can see some of the names in the "Target.Value" are the same as sheets in an array. The ones that do are lead schedules, that is information from other sheets in an array roll into it. That is values from "Pensacola" and "Panama City" flow into "Region 1", the lead sheet.
What I am trying to do is finish the code where based on the "Target.Value" selected it automatically takes the user to the lead sheet. For example, when the user selects "Region 1" in the data validation it takes the user directly to the sheet "Region 1". The user of course can also see the other sheets in the array, that is "Pensacola" and "Panama City" and can manually select the tab to view or go into one of the later
Private Sub Worksheet_Change(ByVal Target As Range) Dim sh As Worksheet If Not Application.Intersect(Range("B10"), Target) Is Nothing Then For Each sh In Sheets(Array("Panama City", "Pensacola", "Region 1")) sh.Visible = Target.Value = "Region 1" Next sh
on sheet1 I have a button I need to do the following when clicked: (1) name the next 30 sheets based on cell values in sheet1 (2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.
The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".
The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?
As an extra point, I'd like to use column B to check for blank rows (not column A).
Is there a way to have a user on sheet1 pick from a pull down of a data validation on sheet2? I have important information that is linked to only sheet2 within a data validation. I want to be able to reference it in sheet1 to have the user pick, then it toggles them on sheet2. Is this possible?
A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.
Sub Button10_click() Worksheets("Account Type").Activate End Sub Sub Button11_Click() Worksheets("Name Change").Activate End Sub Sub Button12_Click() Worksheets("Address-Phone").Activate End Sub Sub Button13_Click() Worksheets("Main Menu").Activate End Sub Sub Button15_Click() Worksheets("Cust-Owner").Activate End Sub Sub Button16_Click() Worksheets("Misc").Activate End Sub
I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.
Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.