Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Toggle To Hide & Show Rows Based On Conditions


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Toggle Show & Hide Controls In Range
Having a problem with a survey I'm creating in Excel.

I have a question with a Yes/No response cell. I want to show/hide several rows below it. The problem is that these rows also contain 6 option buttons, which I also need to hide/show depending on the response cell.

Is there a handy way to hide all of these shapes/controls in the defined range, and then "unhide" them if the cell value changes back to "Yes"?

View Replies!   View Related
Hide & Show Rows Based On Cell Value
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 Replies!   View Related
Automatically Hide / Show Rows Based On Cell Value
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 Replies!   View Related
Hide & Show Rows Based On Multiple Conditional Formatting
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 Replies!   View Related
Automatically Hide & Show Rows Based On COUNTIF Result
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 don’t hide the row
If the value is “0” or “ “ then hide the row
Sheet2
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet3
If the value in Range BE11:BE160 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range O1:O150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row
Sheet4
If the value in Range B1:B150 equals 1 don’t hide the row
if the value is “0” or “ “ then hide the row

View Replies!   View Related
Toggle Hide/Unhide Rows
I have a macro that works fine with just numbers as a test run when I use it in the desired workbook/worksheets (where the values are returned via Indedx/match functions) there appears to be a small problem. (Below is the relevant post.)

[url]

Background is that I load raw data into a monthly account sheet which has the appropriate cost centre codes for allocating expenses.

I then use a vlookup in combination with indirect to place the summary data by costcentre code into annual worksheet.

I then have a summary sheet for printing purposes so I can print out a single month expenses by code to staple to the appropiate monthly costcentre statement. This is done by using Index & match functions looking at the annual summary sheet

What seems to be happening is that the code is sometimes recognizing formulas as a value & therefore showing zero values when I want only values 0 (as I sometimes have minus values).

Also sometimes when I go to unhide the all the rows it also is recognising some formulas as having value.

I am using Xl 2003.

View Replies!   View Related
Toggle Button To Hide/unhide Rows
I have a command button that hides rows based on zero value in Col B then a 2nd btn to unhide those rows. The challenge is that as I want this code for several workbooks that will have data of varying row lengths & not all the zero values will be in the exact same place for each workbook or each month. Having done a little research on the Board (& googled) I have not found an answer to my challenge. So I am sure someone will point me in the right direction -

Private Sub CommandButton1_Click()
BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = True

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Range("A2").Activate................................

View Replies!   View Related
Using Conditions To Show/hide Worksheets
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.

One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.

If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.

View Replies!   View Related
Hide Rows By Conditions
Is it possible to close rows based on conditions? e.g if A1 contained Yes or No as the switch to close and a column contains open or closed in each row for the trigger. So if A1=Yes all rows with 'closed' will be hidden, if A1=No then all rows are shown

View Replies!   View Related
Show Hide Rows On Click
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 Replies!   View Related
Macro Hide / Show Rows
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 Replies!   View Related
Dynamically Hide/show Rows
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 Replies!   View Related
Hide Show Blank Rows
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 Replies!   View Related
ToggleButtons For Hide And Show Rows
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 Replies!   View Related
Automatically Hide/Show Rows Across Worksheets
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 Replies!   View Related
Hide Or Show An Image Based On A Cell Value
I have a spreadsheet with two bitmap images inserted into it. In cell E2, I will enter a number, either a 1 or a 2. if I enter a 1, I want only the first image to be visible. If I enter a 2, I only want the second image to be visible. Is there a way to accomplish this (hopefully without the need for macros)? I've attached a spreadsheet as an example of what I'm trying to do. Also, note that I'd like the images to be stacked on top of each other so that they show up in the same place regardless of wether there's a 1 or a 2 in cell E5

View Replies!   View Related
Hide & Show Sheets Based On Names
I made one file with 13 sheets.

sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn

In Main sheet There are 3 buttons

1 . XYZ
2. abc
3. ddd

when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide

if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide

i don't want to use

Sheet2.Visible = xlSheetVeryHidden

i want to use finde xyz sheet tab name and shows and other are hide.

View Replies!   View Related
Show/Hide Command Button Based On Location
What i am trying to do is make a command button appear when you hit any cell of the row its on. For example, If you were to hit any cell on rows 1 or 2 the command button will appear and when I am not on the visible property goes back to false. I have a ton of buttons on this sheet and I am trying to clean it up so buttons only appear as needed.

View Replies!   View Related
Hide/Show UserForms Based On Workbook And Worksheet
I'm just beginning to work with userforms and have created a couple of forms for workbook navigation. The Menu form is set to appear only on the menu worksheet and the Navbar form is set to show on several database sheets. Both forms are set th hide when the workbook is deactivated.

Private Sub Workbook_Deactivate()
Menufrm.Hide
Navbar.Hide
End Sub

The problem is that when I return to the workbook the forms do not unhide. Using the workbook_activate event causes both forms to show simultaneously. I also have the worksheet_activate event set to show the applicable form.

Private Sub Worksheet_Activate()
Application. ScreenUpdating = False
ActiveWindow.DisplayGridlines = False
Navbar.Show
Navbar.Left = Range("B3").Left
Navbar.Top = Range("B3").Top
Application.ScreenUpdating = True
End Sub

Upon returning to this workbook, I only want the appropriate form to show relative to the worksheet that is active. That is, if I was on sheet2 when I switched to another workbook, sheet2 would be active when I return to the workbook and should show the Navbar form. The menu form should show on sheet1, and the navbar form on sheets 2-5. How do I get the correct form to show when I re-activate the workbook?

View Replies!   View Related
Hide/Show Pivot Table Data Based On Criteria
I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:


Sub FilterDates()

Application. ScreenUpdating = False

Sheets("PnL").Select

Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated

View Replies!   View Related
Hide Show Pivot Column Items Based On Date
I have a pivot table with the column fields as dates. I will like to be able to use VB to dictate which value to show. Here is brief code in which I just recorded...

Sub Button1_Click()
Range("B7").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.PivotItems("10/30/2006").Visible = False
.PivotItems("10/31/2006").Visible = True
End With
End Sub

I would like to show current day only using =today() or something similar. THis would eliminate the user having to modify the pivot tables daily.

View Replies!   View Related
Hide/Unhide Toggle
Could someone provide me with the VBA to 'toggle' between HIDING and UNHIDING columns within one macro

e.g. toggle between this hiding and unhiding the following

Sub HideColumns()

Range("G:G,I:I,AB:AV").Activate
Selection.EntireColumn.Hidden = True

End Sub


View Replies!   View Related
Hide/Show Pivot Table Data Based On Date Criteria
I have looked at the below Hide/Show Pivot Table Field Items help web pages:

Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria

I am trying to use the above, but with dates in the following format in each cell:

YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc

The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.

Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation

View Replies!   View Related
Show/Hide Worksheet Command Button Based On Cell Value Being In Another Range
i have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.

View Replies!   View Related
Hide/Show Sheets Based On Cell Values & Validate Entry
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.

1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.

2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.

View Replies!   View Related
Hide Blank Rows Based On Values In Other Rows
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 Replies!   View Related
Hide/Unhide Multiple Sets Of Columns With Toggle Button
I am trying to create a very large spreadsheet and i have got everything organized, but for easy viewing i want to have a toggle button that hides and un-hides certain sets of the columns (ie. Press down and it hides columns "B-F", "H-J", and "M-O".... then on depress it shows all the information again)

I have got some of the coding down, but am only able to get it to hide one set at a time, not all the sets. Can anyone help me out and let me know how i tell it to run multiple commands from the one press? here is the code i have already: ...

View Replies!   View Related
Writing A Cleaner Loop & Toggle Hide/unhide With Button
What the code does is, when the command button is clicked, if any of the validation list in a particular range is equal to"Select_Product.." or "Select Feature", then hide those rows.

Also, is there a way to toggle hide and unhide rows using one command button. I would like the code to not only hide the rows but when clicked again, it'll unhide it. Is this possible?

Private Sub CommandButton1_Click()

Dim c As Range
Dim d As Range
Dim myRange As Range
Dim featureRange As Range

View Replies!   View Related
Vba To Delete Rows Based On Certain Conditions In Column A
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):

Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub

As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).


View Replies!   View Related
Highlight Non Matching Rows Based On Conditions
I am having a problem finding or creating a macro for what i am needing to do. I have a workbook with 2 worksheets (2 months july and august) to figure out a billing process.

I need to be able to First of all search each value in Column A on sheet 2 (august) to Column A on sheet1 (July).

My sheet is set up like this where ID is a buyer and Products 1,2,3...are if they have purchased that product from us


A B C D
1 ID Product_1 Product_2 Product_3
2 100 0 0 1
3 200 1 1 0
4 300 1 0 0
5 400 0 1 0

Better Picture representation attached

Both worksheets are very similar but August may have some changes from July such as new ID's and changed prduct purchases. What I need to do is have each ID in Column A be compared to Column A in the July sheet, but it may not always be the same cell (because of new ID's added they are moved around)..so it need to search for the same ID first then compare. Once it finds the ID, I then need it to compare the rest of the row (the product info) for that ID to see if any products were added or changed to determine billing. And for any changes that are found in the comparison, I need them to be highlighted.

Now in the case that a new ID was added...it wont be in the July sheet, so the whole row would need to be highlighted to notify of a new ID and new billing needs to be added.

This way the billing person can look at the sheet, see the highlited changes and see what is new or needs to be added to a previous ID's billing. Currnelty they are looking at it manually to see if there are changes.

View Replies!   View Related
Query Worksheet & Return Rows Based On Conditions/Criteria
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.

1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.

2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.

View Replies!   View Related
How To Hide Rows Based On The Value Of Two Columns
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......

View Replies!   View Related
Hide Rows Based On A Criteria
What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

View Replies!   View Related
Hide Rows Based On Date
I need to write a macro that will hide several rows if a calculated date's month is not within the month of the report.

Example:
If month of date in cell B20 is not the same as the month of date in cell A5
then hide rows 20 thru 30 if it is the same month, display rows 20 thru 30.

I need to do this comparison twice. If month of date in cell B40 is not the same as the month of date in cell A5 then hide rows 40 thru 50 if it is the same month, display rows 40 thru 50. The dates in B20 and B40 are the result of a calculations (Date in cell A5 plus some number of days) if that makes any difference. I don't think it should.

View Replies!   View Related
Macro To Hide Rows Based On 'Sum'
I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Following is a code I use to delete a row if that is blank. The difference here is that I dont want to to check first two columns and I want to hide them rather than deleting.

View Replies!   View Related
Hide Rows Based On Color
I have the following code to hide rows based upon whether or not the cell in column A is black or not.

Sub hide_black()

Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("A2:A36635")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = 1 Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub

I was thinking of taking this one step further and having the rows hide automatically once the cell is turned black allowing the user to bypass having to click on a macro button.

View Replies!   View Related
Hide Rows Based On Value In 1 Column
I wrote some simple code to work through a number of rows and hide the rows which contain a value of "XXX". For some reason, when the code hits the line denoted with? it hides the first row of "XXX"'s but then starts all over again at very first line of code outside the loop.

Sheet1. Range("start_1").Select 'cell c53

Do Until ActiveCell.Value = "EOF" 'cell c266
If ActiveCell.Value = "XXX" Then
Selection.EntireRow.Hidden = True '????
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop

View Replies!   View Related
VBA To Hide Rows Based On Cell Content
If I have data in range A1:Z99 I want to write a macro to hide all the rows that have a zero in the A column.

View Replies!   View Related
How To Hide/unhide Rows Based On Cell Value
I'm using column A as a reference. My data starts in cell A3.

Whenever theres a zero in any cell in column A I would like that row to hide.
If the row was hidden and the value changes to any number greater than zero I need the row to unhide.

I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.

View Replies!   View Related
Hide Rows Based On Combobox Selection
I know there are a lot of hide row threads... i've searched for ages... but (unfortunately) i can't a solution that addresses my problem and I'm stuck. Please help!

I have a data between rows 10 and 1310 and I would like to use a combo box option to select and view certain rows of data only. I don't want to use auto filter because i have a separate macro using an auto filter on the same data - they won't play together.

So in short - combo box will contain values from column A. When value is selected from combo box hide all non matching rows based on column A values.

So i have added a combo box to sheet1 and populated it with the following workbook code...

Sub Workbook_Open()
With Sheets("Sheet1").ComboBox1
.AddItem "Select Product Type"
.AddItem "Apples"
.AddItem "Mangoes"
.AddItem "Oranges"
.AddItem "Clothes pegs"........


View Replies!   View Related
Automatically Hide Rows Based On A Value In Column Q
After the user selects something using the validation list in cell F38, the macro should check in column Q in the rows 44 till 425 for "hide". For each row in which it finds "hide" the entire row should be hidden.

I have the code below but it does't work yet. It calculates for ages and afterwards only the first 2 rows of the 381 are hidden (probably because the third row is not "hide").

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim ThisCell As Range
Target = Range("F38")

Set MyRange = Range("Q44:Q" & Range("Q425").End(xlUp).Row)
For Each ThisCell In MyRange
If ThisCell.Value = "hide" Then
ThisCell.EntireRow.Hidden = True
End If
Next ThisCell
End Sub

View Replies!   View Related
Hide & Unhide Rows Based On Cell Value
I realize there are many Hide/Unhide requests. Although, believe me when I state that I have checked into the matter, and tried to solve it on my own.

If any of you fine forum dwellers could point me in the right direction, I would greatly appreciate it.
I am trying to achieve the following:

Two Macros:
#1:
To hide rows(entire spreadsheet) based on cell value in Column C (value is '0')
Here is a code (from this forum) that seems to be the least complex/confusing. I don't understand the "AC2" value. Would it not be A2? Or does it signify a certain range?


Sub HideRows()
With Sheets("Store Snapshot")
If Range("AC2").Value = 2 Then Rows("13:15").EntireRow.Hidden = True
If Range("AC2").Value = 1 Then Rows("13:15").EntireRow.Hidden = False
End With
End Sub

#2:
To UNHIDE the rows that Macro #1 Hid.

View Replies!   View Related
Hide Rows Based On Cell Value Being Empty
I have read other posts about this but I am still a little confused as most macros that have been made are tweaked to suite the users individual needs.

What I want is something like this to work in the active sheet:

If cells D2:D55 = ""
Then Hide.EntireRow

If cells D2:D55 = "has any value"
Then Show.EntireRow

The values in D2:D55 are populated by a VLOOKUP depending on what someone chooses in a drop down validation list, however not all the rows are always required so I would like to hide them to save some space on my form.

View Replies!   View Related
Macro To Hide Rows Based On 'Sum' Of Certain Cells
I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Means, if the sum of all the cell (except first and second cells in that row) in a row is zero, then that row should be hidden.

View Replies!   View Related
Hide Rows Based On A Cell Reference
How can I automatically hide rows based on a cell reference? For example, If A1=0 then hide row 1.


View Replies!   View Related
Macro To Hide Rows Based On Column Value
I have read several related threads and tried to adapt their answers to my application with no success. I am trying to write a macro that will hide rows based on that row's value in a certain column. Specifically, column AB contains sums and if a sum equals 0 I want to hide that row. The sums start at AB5 and go to AB118 but there are 4 gaps in the column at rows 25, 47, 70, and 94.

View Replies!   View Related
Auto Hide Rows Based On Cell Above
I've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually.

I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up.

I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line.

I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Value = 1 Then

Rows(Target.Row).RowHeight = 0

Else

End If

End Sub

Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped?

Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example?

Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets.

Private Sub Worksheet_Change(ByVal Target As Range)

Cells.RowHeight = 12.27

Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0

End Sub

View Replies!   View Related
Hide Rows Based On Two Cell Values
Is there any way to hide a row if columns 6 and 7 both show either a "-" or a number lower than 0.5? Cells in both rows contain a VLOOKUP formula.

View Replies!   View Related
Hide Rows Based On Drop-Down Selection
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.

View Replies!   View Related
Hide/Unhide Rows Based On 1 Cell
I'm trying to find a way to hide rows based on a condition in one cell.

Basically I have a pull-down list with Yes or No in it. I want to hide rows 52 through 57, if No is selected, and make the rows reappear if Yes is selected.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved