Hide Worksheet That Contains Over 15k Rows
Mar 12, 2009
I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.
I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.
I don't know exactly if I should use a checkbox, listbox, combo box ect.
View 9 Replies
ADVERTISEMENT
Jun 19, 2014
I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?
View 3 Replies
View Related
Apr 30, 2012
I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.
I have put this code on the features segment tab but it is not working.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "B40" Then
[Code].....
View 3 Replies
View Related
Sep 7, 2006
This code will hide row 2 across Sheet1, Sheet2 and Sheet3 while recording in the macro record mode but when played back after recording will only hide row 2 on Sheet1. How do I get this code to work?
Sub MultiplePageHideRows()
Rows("2:2").Select
Sheets( Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Selection.EntireRow.Hidden = True
Range("A1").Select
Sheets("Sheet1").Select
End Sub
View 5 Replies
View Related
Apr 9, 2014
Getting a macro to work. I've looked through the forums pretty extensively but ad I'm not too hot with the old vba, I haven't been able to get it working.
I have two worksheets in the same workbook. The first worksheet, let's call it Input, is one for data entry; and the second one, let's call in Output, is formatted for printing.
There are 8 drop down boxes from a data validation list, that when a particular option or three are selected, I need the Output worksheet to unhide only the rows associated with those options.
In trying to get this all to work, I'd added a function in the cell to the left of each option in the Output page that will show the text "show" when that option is selected on the Input page, or the text "hide" if not selected.
I think I could do this with some time with a clunky and long macro, but would prefer to us some kind of "for each" option to hide rows that have "hide" shown in column A, as I'm looking at a range of 100~ cells.
View 3 Replies
View Related
Mar 7, 2014
Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.
View 5 Replies
View Related
Oct 28, 2009
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem:
What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following
Sub
If Target.Value = 1 Then
Rows(Target.Row).RowHeight=0
Else
End If
End Sub
Which I have changed to:
Sub
If F4.Value = Green Then
Rows("5,6,7,8,9,10.Row).RowHeight=0
Else
End If
End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?
View 9 Replies
View Related
May 26, 2014
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
View 14 Replies
View Related
Jun 9, 2013
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
View 4 Replies
View Related
Nov 3, 2006
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View 2 Replies
View Related
Nov 15, 2013
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub
View 3 Replies
View Related
May 14, 2014
I want activation of userform to hide worksheet, but as soon as the Userform is closed, the worksheet should show.
I have attached file to aid.
View 1 Replies
View Related
Jan 31, 2007
How to hide a worksheet? ... if I hide it will I it still be able to copy it?
View 6 Replies
View Related
Aug 14, 2007
I want to know how do I hide my "Raw Data" worksheets? I have about 10 worksheets in a workbook and just need to hide the first one - where all the raw data is store...
View 9 Replies
View Related
Jan 30, 2009
We're trying to create an invoicing sheet with MS excel for our sub contractors but we also want the information they input to be transfered onto a second work sheet within the work book that has our mark up added.
Problem is that we don't want our sub contractors to see the sheet with the mark up. Is there any way to hide a work sheet or to limit access to the work sheets some one can veiw with in a workbook?
View 2 Replies
View Related
Oct 7, 2009
I have an excel workbook that has multiple worksheets that is linked to our in-house system via ODBC and it refreshes every 5 minutes with up to date sales data.
I am looking to output just one of the work sheets onto a large LCD display / wallboard.
I need to just display the summary worksheet without the excel toolbars / gui
View 14 Replies
View Related
Mar 30, 2004
I put the correct cell in place $K$23, now I want to hide a worksheet called "DutyCode" when info!$k$23 is blank or has "xx" or "XX" in the cell, and then unhide "DutyCode" when the number 27 is inputed in info!$k$23
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$k$23" Then Exit Sub
If Target.Value = 1234 Then
Worksheets("Sheet2").Visible = True
Else
Worksheets("Sheet2").Visible = False
End If
End Sub
View 5 Replies
View Related
Apr 1, 2009
In excel I would like to create a macro which wil be able to hide all lines were a text (to be defined in a cell for example) is not present
Here is an example:
linesdata:X#1#2a#3b#4b#5a#6a#7c
Pending X value lines #2 to #7 will be hidden or not
View 9 Replies
View Related
Oct 20, 2006
How do you hide a sheet?
View 6 Replies
View Related
Mar 16, 2007
I've done a search for this but cant find the complete answer for what I'm looking for. Basically I've got a "Home Sheet" set-up where the user can only navigate to worksheets via the buttons on the home page (with a short- cut to go back to the home page). It works ok (very basic though) but the user can still use the worksheet tabs at the bottom of the screen to navigate (making this system void).
how I can, either, hide the worksheet tabs at the bottom of excel, or, a better way of navigating these worksheets (there is only about 6 of them).
View 2 Replies
View Related
Dec 18, 2007
To hide a worksheet upon open I use: wsSheet.Visible = xlSheetVeryHidden. What do I use to hide a chart which is its own worksheet?
View 3 Replies
View Related
Jul 16, 2014
knew if there was a way to hide the available tabs in an excel worksheet.
I know you may be thinking just right click and hide, however what I am looking for is a little different.
Basically I have a Macro Enabled work sheet which works on a "one click" system. The work sheet is full of buttons with assigned macros and data validation drop down boxes which link to many different tabs. The entire sheet is for information only so I will be locking the sheets to avoid data manipulation. The problem with right clicking and hiding the tabs is that it interfears with the macros.
All I really want to do is make these tabs not visible. I have set the sheet to open in full screen mode and they obviously are still there, but this is basically what I am looking to do. Not so much hide them in terms of sending them into the background, more so make them "drop off" the bottom of the screen to they are still active but not visible.
View 6 Replies
View Related
May 22, 2009
I am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.
View 4 Replies
View Related
Jun 19, 2009
If Sheet1!A1 = FALSE, I want to hide Sheet2. If the value of Sheet1!A1 changes to TRUE, I want Sheet2 to be visible.
View 2 Replies
View Related
Feb 27, 2007
Is there a way to either hide or disable the worksheet menu bar on ONE SHEET ONLY, so that a right-click customize doesn't even work on that sheet. I want the user to be able to do nothing but what my customized toolbar allows on that sheet.
View 5 Replies
View Related
Aug 7, 2008
Is it possible to hide specific worksheet tabs.
I have a workbook with many worksheets and I only want to hide Sheet1 and Sheet2.
View 9 Replies
View Related
Aug 8, 2009
I am trying to write a code that would hide when certain cells in the worksheet are empty. Also the sheet name should start with a -.
This is the code i have so far. The thing is that the sheet will always hide. The criteria on the cells doesnt work.
Sub Hide_all_filled_Templates()
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If Left(ws.Name, 1) = "-" Then
If Not Range("I9").Value = "" Or Range("K9").Value = "" Or Range("M9").Value = "" Or Range("O9").Value = "" Then ws.Visible = False
End If
Next
Application.DisplayAlerts = True
End Sub
The code now hides all shees starting with "-". It does not take the cell criteria into account. Why?
Other point is that these cell references are just a few of what it should really be. How do I make this easier for myself to write the code. Point is that these cells come in row 9 (like the example) then in row 11 then in row 15, 17, 21,23, etc. Also the columns jump with uneven steps.
View 9 Replies
View Related
Sep 18, 2008
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?
Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub
Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?
View 3 Replies
View Related
Oct 14, 2006
I got two worksheets visible for user to enter information with vba codes behind the sheets(i.e. Private Sub Worksheet_SelectionChange(ByVal Target As Range). Most cells of the worksheets are protected if the vba is executed. However, it was found that if user chooses not to run the macro when prompted, the worksheet can be edited whatever user want. So, how can the worksheet whole worksheet is protected if macro is not run?
View 9 Replies
View Related
Jul 9, 2007
I need to be able to save a copy of my spreadsheet but protect the structure so that the hidden rows cannot be opened by the person that I e-mail it to. At the moment I am using the current
Private Sub CommandButton2_Click()
Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName
Call ProtectRobin
End Sub
Private Sub ProtectRobin()
ActiveWorkbook.Protect ("Robin")
End Sub
The saving part of the macro is working perfectly, however I cannot get the protection to work. It must have a password - I do not want the receipient to be able to go 'Tools' > 'Unprotect Sheet'.
View 6 Replies
View Related
