Excel 2003 :: Show And Hide Rows - Use Subtotals?
Jul 24, 2014
I have a seating plan for my students that has all sorts of data in it and I want to hide certain rows from students. However, they are not all in a group, they are spread out. So for example I have row 3 with student name, row 4 with their target grade, row 5 with their current grade, then row 6 is another student with data on 7 and 8. After that I might have a gap and then the next student on row 10 with data on 11 and 12. I want to toggle on and off the data and leave the student seats. I'm thinking that Subtotals would be the best way, but not sure how to implement it. Do I put row headings on the side? Also I haven't used subtotals since Excel 2003.
View 1 Replies
ADVERTISEMENT
Jan 28, 2012
In sheet 1, I have a list of data in A2:D21. In sheet 2, I have formulas in the same range that "paste link" the data. If, there is no data entered in any entire row of the same range in sheet 1, then I want the entire row to hide automatically in sheet 2. In fact, unless there is something entered to start with, I do not want any of the rows to be visible. Is this possible and how?
View 2 Replies
View Related
Oct 30, 2011
Operating System: Windows XP, Excel version: 2003
Aim: To create a Macro to hide all rows where the date in column D is before today. Column D has about 600 rows.
Current solution:
Code:
Sub Hide_Old2()
'Worksheet name
With Worksheets("Schedule")
'set start of date range
Set rngStart = .Range("D2")
'find end of date range
[Code] .....
The problem with this solution is its speed, or lack thereof. It causes the screen to hang and flicker while it cylces through. Is there some way to create a range based on the date and hide the range? or another solution?
Note: Autofilter is not an option, as the spreadsheet with the dates needs to be kept simple for other stakholders and the macro is being run from another sheet.
View 3 Replies
View Related
Aug 8, 2014
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
View 14 Replies
View Related
Feb 8, 2013
Excel 2003.
Trying to show the first day in a month.
B4 shows 8-Jan-13
A2 has formula =MONTH($B$4) which returns 1-Jan-00
Both cells are formatted as dd-mmm-yy. So why is A2 showing the year as 00?
View 4 Replies
View Related
May 10, 2012
I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
View 5 Replies
View Related
Jan 5, 2007
Is it possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have uploaded an example excel spreadsheet : example show hide.xls
View 9 Replies
View Related
Jul 2, 2009
I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.
I'm currently using rows 1:700, but I may add to it.
View 9 Replies
View Related
Feb 17, 2010
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
View 9 Replies
View Related
Jun 26, 2006
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using.
Private Sub ToggleButton1_Click()
With ToggleButton1
.Caption = "Goal 1"
End With
Dim rCell As Range
If ToggleButton1.Value = True Then
For Each rCell In Range("P2:P99")
rCell.EntireRow.Hidden = rCell > 1
Next rCell
3) Else
Range("P2:P99").EntireRow.Hidden = False
End If
End Sub
I have three questions -
1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3.
2) Is this code an efficient way of doing what I want?
3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.
View 2 Replies
View Related
Jan 5, 2007
if it is possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
View 9 Replies
View Related
Jan 9, 2007
I have a lengthy column containing text information. Within this column are various gaps. ie: several cells with no information.
eg:
..... Row F
1...text info
2...text info
3
4
5...text info
6...text info
7
8...text info
I would like to create a simple macro (switched via toggle switch) whereby it hides/unhides the rows containing cells with no text information.
It should also be mentioned that this column contains various background color formatting, for both empty and text cells.
View 4 Replies
View Related
Apr 17, 2008
When I call a series of subroutines from different worksheets, ScreenUpdating = False is not working.Here is my
Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.Calculation = xlManual
.EnableEvents = False
End With
If ActiveCell = "No" Then
Call Sheet3.DisableTBs
Call Sheet4.DisableTBs
Call Sheet5.DisableTBs
Call Sheet6.DisableTBs
Call Sheet8.DisableTBs
Call Sheet9.DisableTBs
Call Sheet10.DisableTBs......................
View 3 Replies
View Related
Jun 10, 2008
I have two sheets say:
Sheet1
Sheet2
Sheet1 has a few comboboxes saying (YES / NO) conditions Which are assigned to particular cells (for Ex: say Combobox1 value assignes to Sheet1!B5 )
If Sheet!B5 = YES some rows in Sheet2 Say ( Row12 ,Row 15,Row 16) has to be hide.
I will add a command button to sheet1 and call macro if i click command button checking the conditions in sheet1 combo boxes..rows in sheets2 has to hide..
View 4 Replies
View Related
Aug 5, 2012
Have Excel 2003. I have
List of email addresses in column a
List of email addresses in column b
** I would like a formula that will take all the values in column A and compare it to the entire list of entries in column B.
Would like it to show in Column C any entries in Column A that ARE NOT in the entire column B.
*Would like it to repeat for entries that are in Column B, but not in Column A and show in D.
*Then would like a count of the differences for each column (that is pretty easy).
It needs to look at the entire list of entries in the column as these will be email addresses. We want to know what is missing from Column A that is not in Column B and what is missing from B that is not in A.
View 3 Replies
View Related
May 7, 2013
Trying to have A1 in Sheet1 having three values 1,2,3 if A1 = 1 hide rows from 1:5 and 10:1500 in three Sheets2,3,4
View 8 Replies
View Related
Dec 2, 2013
I have a spread sheet in which some rows are grouped.
So I can see the + (or -) buttons on the side, and the 1 and 2 buttons on the top, to hide or show the separate grouped rows.
Now, depending on the value of a certain cell, I either want to show or hide the grouped rows.
So actually I want to control the +/- and 1/2 buttons indirectly.
Is this possible? Or should I do this by hiding rows instead of grouping them?
View 2 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
Feb 27, 2008
I wrote a small code to hide some columns if a certain cell is equal to a certain string.
The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.
Sub Auto_Open()
Range("A1").OnEntry = "Action"
End Sub
Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
View 3 Replies
View Related
Mar 4, 2008
First, I realize there are plenty of hide cells threads but I have yet to find one pertaining to my situation and I apologize in advance if I this solution has already been posted.
The Problem: I am looking for macro code to a toggle button that will hide various rows that have no value between multiple sets of rows. The toggle should refresh the format of the rows as the information that was blank could later on have value.
The Setup: For each set of rows, the first row will have the label T and the last will have the label S. In between T and S there can be data. If the cells between T and S are all empty then the rows including T and S should be hidden other wise only the non blank cells between T and S should be visible.
The Reason: I have a master database worksheet, there are four copies of the master database worksheet each entitled phase1, ""2, ""3, & ""4 respectively. These phase sheets are linked to the master sheet and show the entire row's data based upon the beginning cell of each row showing either phase1, ""2, & so on.. The data is broken up into many sub databases and traditional auto filters or advanced auto filters will not be applicable as the title of the data and the empty rows in between need to be hidden if the data is empty.
View 9 Replies
View Related
Jul 27, 2013
I've seen excel sheet view like this [URL].....
View 2 Replies
View Related
Nov 12, 2013
I found this code for a button, so I can collapse and expand a set number of rows within that sheet. It works exactly the way i want it to, however, now I have a second sheet in my workbook, and I used the same button. Problem here is that when I activate the button, it opens the same rows in EVERY sheet in the workbook. "For each ws in Worksheets" so my question is what is the term for it to only work on a certain worksheet?
Code:
Private Sub ToggleButton1_Change()Application.ScreenUpdating = False
Dim ws As Worksheet
With ToggleButton1
If ToggleButton1.Value = True Then
For Each ws In Worksheets
[Code] ........
Other questions is about the ability to send an MS Outlook email from inside of excel.
I've been trying to find something that does the following:
Click button, Form pops up, has drop downs to select recipient, has field for subject, has field for message body, sends email.
View 2 Replies
View Related
Oct 26, 2008
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns
and i need the macro to be fast
Sheet1
If the value in Range BE11:BE160 equals 1 dont hide the row
If the value is 0 or then hide the row
Sheet2
If the value in Range BE11:BE160 equals 1 dont hide the row
if the value is 0 or then hide the row
Sheet3
If the value in Range BE11:BE160 equals 1 dont hide the row
if the value is 0 or then hide the row
Sheet4
If the value in Range O1:O150 equals 1 dont hide the row
if the value is 0 or then hide the row
Sheet4
If the value in Range B1:B150 equals 1 dont hide the row
if the value is 0 or then hide the row
View 3 Replies
View Related
Aug 6, 2009
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference:
[url]
The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...
View 3 Replies
View Related
Jul 18, 2007
Does anyone know a setting to display pivot tables as a % of sub total automatically within the Pivot Table Settings? I have recreated a formula on the cells H:K of what I am looking for. see Attached.
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
Jul 8, 2014
I have created a UserForm that has a ComboBox and depending on the number selected I want it to show that number of Labels/TextBoxes...
So if I select "0" nothing is shown, if I select "1" one set of Labels/TextBoxes is shown, select "2" and two sets of Labels/TextBoxes are shown... but also if I have selected "2" and then select "1" I want the second set to be hidden again...
Also I know I should have renamed the Label/TexBoxes to make it easer but I was adding things and making it up as I went along...
I'm using Excel 2010 on windows 7.
Code:
Sub UnHide_NewRoutings()
If (Engineering.ComboBox2.value) = "0" Then
Engineering.Label4.Visible = False
Engineering.TextBox5.Visible = False
Engineering.Label9.Visible = False
Engineering.TextBox9.Visible = False
[Code] ..........
View 3 Replies
View Related
Feb 20, 2013
Running excel 2003 and have a number of sheets where i want buttons along the headings to sort a selection of rows.
For Eaxample one sheet i have data in range A6:AD135. i would like to assign a macro to various buttons on row 5 to sort by that column ie button in column b sorts all data in the range by column B.
View 4 Replies
View Related
Jul 31, 2013
I am running MS Excel 2003.
A user inputs a string of letters/symbols all meaning different things. These would typically for my case be L, R, +, -, F. These items correspond to certain behaviors. Essentially I'm working on a L-System solver for those of you who are well versed in math. For those of you who aren't, I'll explain a bit.
An L-system starts with a string, for example:
LFL+F+LFL
and then has one or more generators:
L:-RF+LFL+FR-
R:+LF-RFR-FL+
then at each iteration of the system, every L and R in the previous iteration gets replaced with that string. The +/- are incremental angles.
I need to be able to take each iteration and convert it into a series of rows with one character in each row. I need it to do this automatically, and automatically adjust to the proper length of rows (these strings can get thousands of characters long because each iteration of the system converts the Ls and Rs into longer strings containing more of all of those letters.) Once each of those rows are constructed, I need it to run some mathematical operations on each of these rows automatically.
For example, for each + or -, the value in an angle column should change by an increment given elsewhere in the sheet. For each F, the x and y coordinates should change from the previous iteration based on the angle (just a basic d+dcos(ang) or d+dsin(ang) where d is a distance multiplied by a scaling factor).
I have no experience with Macros or any type of scripting, so if I can keep this in an Excel formula, that would be ideal. I know a minimal amount of C++ and Python, but that's the extent of my programming knowledge.
View 1 Replies
View Related
Mar 20, 2014
I have a master list of Players on one sheet B2:B72 and in E2:E72 is a column called Playing and in it is "y" or "n". I need to make a list of the Players that are Playing on another sheet without blank rows (I can do it but it leaves blank rows for the Players that have a "n" from column B. I am using 2003
Here is the formula I am using now: =IF(Players!$E3="y",Players!$B3, " ") but I get blank rows for the player that are not playing.
View 3 Replies
View Related
