Hide Specific Rows
Jan 9, 2008
I have an Excel File Ulip21.xls with several sheets in it. In the “INPUT” sheet, I have two cell “O9” and “O10”, where I input two values.
In Cell O9, I input the “Enter Term Of a policy”, which ranges between 1 and 50.
In Cell O10, I input the “ Fixed premium per year (Yes / No ) “ , where the answer is either Yes or No.
If the input in O10 is Yes, then rows 31 to rows 82 should be hidden as those are the rows, where I input irregular premiums.
Now I want the following …..
If the value of O10 is Yes, then rows 31 to 82 should be hidden ...
View 9 Replies
ADVERTISEMENT
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
Sep 13, 2006
I’ve got a spreadsheet with approximately 30,000 rows and I would like to create a macro which hides every row which has not the value 12, 14 or 19 in column I. What is the best way to do it? With an if statement?
View 4 Replies
View Related
Apr 8, 2008
Cell A1 can be left blank or have any value 1 to 99999 inserted by the user. Default value is null. My data starts on row 5 and currently goes to row 62, but will include more rows over time. I want to automatically hide rows 5 to 62 (and higher later on) where the value in Column C (C5, C6, C7, etc) is less than or equal to the value in A1 as long as A1 isn't null. If A1 is null, then no row hiding would be done. Ideally would be great to have msg also that says "No rows hidden" or "10 rows hidden", but not critical - just nice to have if you can provide such easily and quickly otherwise I'll just try to learn it later on.
View 7 Replies
View Related
Jun 10, 2014
I have the following macro to autohide some rows with no data, however its remove rows starting from column A.
[Code] .....
How do i fix this to only hide the rows in the range (H4:V50)?
View 5 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
Feb 22, 2009
Basicly I used conditional formatting and lots of "IF".My solution lacks in style and it's time consuming ..
Long story short: I need to modify the way some cells are displayed based on the selection in a specific dropdown list.
I need that whenI select Task3 from the dropdown list next to "step1: please select" , everything from row 15 to row21 (both 15 & 21 rows included) and from row 23 on, to disappear.
View 9 Replies
View Related
Mar 24, 2014
I have a workbook which contains 8 worksheets. I want to able to run a macro that looks for specific words in column A of each worksheet and hides any rows in between the specified words. The following code works except if a worksheet does not contain the specified words. Is there a better way to accomplish this?
Attached is an example of the spreadsheet. The code below works just fine on the tabs highlighted in green, but halts on the tab highlighted in red. The tab highlighted in yellow is showing you the rows I need to hide.
Example.xlsx‎
View 5 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
Dec 10, 2007
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
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
Jan 27, 2009
Is there a way to hide certain cells from printing? The cells can be seen when working on the sheet, but when it is printed, the cell are hidden/have no values.
I print normally from the file-menu, so a print-macro can't be used for this.
Somekind of script to ThisWorkbook so it is automatic?
View 9 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
Jan 19, 2007
I have a row in a table from columns C to CZ. All the cells in the row contain a formula, between 1 and 204 cells in the row will have a value (i.e. will not be blank).
The cells with a value will start at column C and may or may not have a blank cell before no more values and blank cells to the end (Col GZ).
Example:
C D E F G H I J K >..........................GZ
23 34 67 74 2 34 6 2 56 all blank ("") to end
or:
C D E F G H I J K >..........................GZ
23 34 67 "" "" 34 6 all blank ("") to end
I need to hide the entire columns when the cells in this row are blank but NOT if the blank cell has valued cells after it (i.e. do not hide columns F and G in the second example.
I can do this by looping back from col GZ and hiding the columns one at a time, which is very slow. I am stuck on the code to select all the relavent columns and hide together.
View 4 Replies
View Related
Jul 10, 2014
I have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.
What I want to do
- I currently have two tabs in a userform - one called 'main' and one called 'extra'.
- I want Main to always be shown to the user.
- I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition
I have a question on my userform (using Option Buttons):
"Did the customer ask about an extra product today?"
If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet
If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.
In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.
View 6 Replies
View Related
Jul 13, 2009
I am looking or a code, (Or formula if one exists) to hide or un-hide columns depending on what is entered into a cell. I have attached an example to help clarify what i mean. Basically i want:
If A2 is empty, hide columns C, D, E & F
If A2 = Apple, Unhide columns C & D, but keep E & F hidden
If A2 then becomes Banana, rehide C & D and unhide D&F
View 2 Replies
View Related
Oct 24, 2006
Need the VBA script to hide/unhide specific columns within a worksheet.
View 9 Replies
View Related
Jun 12, 2008
I have a spreadsheet with four columns of data for every month (i.e. January has a Prior Year, Budget, Outlook, and Actual column). I have been trying to come up with a macro or form that will allow me to customize which columns I would like to see and hide the rest. For example, if I chose to see the Budget and Actual columns, it would hide the Prior Year and Outlook columns for each month.
View 2 Replies
View Related
May 12, 2014
Basically I have a big table containing several columns for the different quarters. (Each quarter has several columns which are not all next to each other)
I would like to use 4 checkboxes which the user can select the show or hide the columns for that quarter. E.g. if Q1 and Q3 are checked only those are visible)
(if quarter 1 clicked than all columns are visible if unclicked hidden).
The code I have written workes well for the first checkbox (Q1), but for the other quarters (Q2, Q3 and Q4) the columns get hidden when checked (which should be the other way round) and don't get unhidden when unchecked.
Sub CheckboxQone()
If Range("$A$1").Value = True Then
Call showQ1
Else: Range("$A$1").Value = False
Call hideQ1
[Code] ...........
View 2 Replies
View Related
Apr 7, 2012
I'd like to be able to search a spreadsheet for a specific word or series of characters. Once all of the ROWS that contain the characters are found, I'd like to be able to have another spreadsheet with ONLY those rows (all columns).
View 8 Replies
View Related
Jul 17, 2014
I have a worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.
[Code] .....
View 9 Replies
View Related
Oct 6, 2008
I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.
View 3 Replies
View Related
Feb 22, 2013
I preferrably want to hide all rows from row 58 and down, but then unhide some rows. The rows that I want to unhide is from row 1000. How many rows that I want to unhide, depends on the content of that list. If the list is empty, then I want to unhide rows 1000-1003.
What I have written (but doesn't work), is:
Code:
Range("B58:AG1048576").Select
Selection.EntireRow.Hidden = True
If Range("C1002") = Empty Then
Erstatningsgrunnlag = Range("B1000:AG1003")
Else: Erstatningsgrunnlag = Range("C1001:AG") & Range("C1001").End(xlDown).Row + 1
End If
Erstatningsgrunnlag.EntireRow.Hidden = False
View 3 Replies
View Related
Oct 29, 2013
Rows 9-79 - if value in column D is zero, then hide row.
How do I do this?
Also - can this be triggered by just selecting the worksheet, or will I have to use a button?
View 4 Replies
View Related
Aug 21, 2007
Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually.
View 9 Replies
View Related
Jan 8, 2008
I have a worksheet "ULIP21.xls", where in cell C10, the value can be either "Yes" or "No". If the value is "No", I want the rows 31 to rows 82 hidden in the sheet "INPUT" of the worksheet.
View 9 Replies
View Related
Feb 13, 2009
I want to make a macro that will recognize a value in a cell and then hide
rows that I don't need.
For example: cell is Y2
If I put into the cell the value of "abs" i whant to hide rows from below that do not contains(the rows are Y5 :Y25) "abs"
View 9 Replies
View Related
Jun 17, 2009
i've run a quick search on the forum and have picked up a few ideas but wonder if someone can help write some code?
I need rows 10:14 to hide in sheet2 if cell b13 in sheet 1 is blank?
View 9 Replies
View Related
Sep 13, 2009
I'm creating a roster which effectively needs 5 discrete pieces of information per cell - one main piece and 4 notes.
The creator of the current worksheet used comments for this function. They can't be printed, searched etc, you can't work with them and because the info can't be added with data validation it's inconsistent. What a nightmare.
I've rejected the 3d option across sheets because of the complexity and limitations of 3d calculations.
My solution is to have the main info in row 1 and the other 4 items in rows 2-5 and then repeat downwards so there are 5 rows per day.
My question is this - is there an easy way for users to hide and unhide the 4 extra rows? I need to improve readability but the extra info needs to be quickly and easily accessed by some users. Everyone else just needs to see what they are doing each day. This roster is a year long downwards so manual hiding and unhiding rows isn't practical.
e.g.:
1camerasounddirectetc>>>2hide details monwho is actually workingmarytomdick3who should have beenmungomidge4why the changesicknessholiday5type of coverovertimefreelance6details of changedouble time invoice no 10247hide details tuewho is actually workingharrymungodick8who should have beenmarymidge9why the changeswapped shiftsholiday10type of coverstafffreelance11details of change40080 invoice no 102412etc13 /
and when hidden, this:
1camerasounddirectetc>>>2show detailsmonwho is actually workingmarytomdick3show detailstuewho is actually workingharrymungodicketc /
View 9 Replies
View Related
Jan 21, 2010
I run a report everyday that I have to sort many times and remove unwanted rows before its down to the data that I need. What I want to try and accomplish is to say that IF column B or C contains WORD1 or WORD2 to hide that ROW that its in. Also if Column D is greater than 400 to hide that row. Better yet delete it! Below would be an example of raw data, then below it what I would only want to show:
Advisory
NIGO
IGO
127.6712/07/2009 10:1612/07/2009 12:23Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
PENDING
125.0712/07/2009 10:2312/07/2009 12:28Retail
IGO
IGO
422.6512/07/2009 10:3112/07/2009 12:34Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
Worksheet would just show:
Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
View 9 Replies
View Related
