Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

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.

View 5 Replies


ADVERTISEMENT

Hiding / Unhiding Rows Based On Cell Input

Jul 2, 2009

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

View 9 Replies View Related

Hiding And Unhiding Rows In Multiple Sheets Based On Specific Cell Value With VBA

Jan 4, 2013

I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?

For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.

View 9 Replies View Related

Hiding/Unhiding Rows

Feb 8, 2010

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.

View 5 Replies View Related

VBA For Hiding/unhiding Rows

Sep 27, 2007

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.

View 9 Replies View Related

Hiding And Unhiding Rows With Checkbox

Jun 17, 2014

I am trying to Hide and Unhide several rows depending on if several checkboxes are marked or not.

[Code] .....

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.

View 2 Replies View Related

Hiding/unhiding Specific Rows Containing Zero

Jul 23, 2008

I would need two macros for one of my projects. I did search the forum, but I couldn't find anything what would suit my needs.

The first macro should hide those rows which would contain 0 (zero) in a specific column (in my workbook it's E). The secon macro should unhide the hidden rows.

Sound so easy ... but sadly I can't do it.

View 14 Replies View Related

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.

View 4 Replies View Related

Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

View 1 Replies View Related

Referencing A Cell In A Worksheet.

Jul 25, 2006

I used to think I was moderately intelligent. Certainly, I thought I knew my way around a computer. Then I got the bright idea to try and learn VBA. Since then every attempt I've made in trying to produce the most basic of code results in mad fits of rage and me wanting to slit my wrists. I've read half a textbook and I keep trying to write simpler and simpler code to get some sort of result, just to give me the drive to carry on but no matter what I try it always goes completely wrong.
So before I stick me head through the computer screen, here is today's attempt. I want to select a worksheet. I then want to copy the contents of cell A2 down to A1200.

Private Sub CommandButton1_Click()
Sheets("Calls Outcome").Visible = True
Worksheets("Calls Outcome").Activate
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A1200")
End Sub

I’ve stepped through the code and I get an error on “Selection.AutoFill Destination:=Range("A2:A1200")”. After spending several eight-hour days trying to get something out of VBA I have zero energy and zero patience.

View 6 Replies View Related

Hiding Columns In The Worksheet Based On Cell Value

Oct 20, 2006

I have a worksheet that has >10 rows of data and over 150 columns. The values in the cells for individual row include NA, NE, D, A and S. Each row will have one or all of these values in one of the cells. Here is the question:

How do I hide columns containing 'NA' in the cell for a particular row, when only that row is selected by clicking on any cell in row 'A'? For example: If my row 3 cell values for column A, J, R, and X are 'NA' I want to hide the column containing 'NA' only and display everything else? And only when I click on row 3 column 'A'

View 2 Replies View Related

Tab Named By Referencing A Cell Within A Worksheet?

May 6, 2008

Is it possible to have a tab named by referencing a cell within a worksheet?

View 9 Replies View Related

Combining/merging Multiple Rows From One Worksheet Into One Cell In Another Worksheet

Jan 28, 2010

I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.

This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.

I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.

I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.

View 12 Replies View Related

Referencing Cell - Populate Another Worksheet With Their Respective Values

Mar 24, 2014

I am working with a worksheet that has some cells merged e.g. A1 and A2 are merged together as 1 cell. I am using cell offset to reference values from the worksheet assign them to variables then populate another worksheet with their respective values. I noticed that cell.offset(row,column).value is not retrieving a value when merged cells are mixed with non-merged cells within a given row. Is there another method/function I can take advantage of here?

View 1 Replies View Related

Hiding / Unhiding Columns

May 20, 2007

I have a spreadsheet that i would like to hide all columns from B:M and to select the column/s I want to unhide.

I would like VBA cose to do this or to set up a combo box that will allow me to hide/unhide specific columns....

View 9 Replies View Related

Hiding And Unhiding Two Sets Of Worksheets

Oct 11, 2008

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

View 11 Replies View Related

Hiding And Unhiding Sheets Using A Macro

Apr 27, 2012

I would like to be able to hide the tab i am in and unhide another tab at the click of the button,

I have tried recording the macro myself and it works up to a point,

I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)

View 4 Replies View Related

Hyperlink VBA Hiding / Unhiding Lines?

Feb 27, 2013

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.

View 9 Replies View Related

Hiding & Unhiding Tools / Macros

Jan 26, 2007

I want to hide and unhide tool / macros menu so that users don't run the macros in the workbooks.

View 9 Replies View Related

Code For Hiding Or Unhiding Columns Based On Row Values

Feb 2, 2007

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".

View 2 Replies View Related

Importing Data Conditionally From 1 Worksheet To Another

Apr 3, 2009

I used to know my way around Excel pretty well back in college, but I'm drawing a blank here several years later. I know there's a way to do this, but can't remember how.

In the attached sheet, I have info on Sheet 1. Sheet 2 only displays the info with LABEL=2. How can I make this sheet 2 automatically do this and update based on changes to Sheet1?

View 4 Replies View Related

Copy Conditionally Highlighted Cells And Their Fields To Another Worksheet?

Jan 26, 2013

I'm currently working on a maintenance task sheet for a couple of generators. I have successfully created button macros to input dates. I would like a macro to copy all the cells that are RED (due to a condition rule) plus their associated fields to the left from worksheet "Schedule" to worksheet "Tasks Due". [URL]

Here's the pre-programming flow, I just don't know the code to do it:

Scan worksheet "Schedule" for red cells in following Range

H8:I16,H18:I22,H24:I35,H37:I41,H43:I49,H51:I59,H61:I72,H74:I75,H77:I84,H86:I89,H91:H100

If cell = red Then Copy Row A:I

Insert Row into sheet ("Tasks Due")
If red cell is in worksheet "Schedule" H8:I16 then insert copied cells in worksheet "Tasks Due" one below cell = Fuel System
If red cell is in worksheet "Schedule" H18:I22 then insert copied cells in worksheet "Tasks Due" one below cell = Lubrication System

[Code] .....

If my uploaded workbook doesn't work I've linked screenshots of my document.

Top of "Schedule" worksheet: [URL] .....
Middle of "Schedule" worksheet: [URL] .....
Bottom of "Schedule" worksheet: [URL] .....
"Tasks Due" worksheet: [URL] .....

View 4 Replies View Related

Conditionally Copy Data: Master Worksheet To Multiple Worksheets

Sep 11, 2006

I am searching for a formula or simple macro to select text data from column named "SM NAME" to corresponding worksheet. I need this to populate as many worksheets as there are SM Names. ( there are usually 10 SMs)

For Example in the Master Data there are Several SM names listed. I need to extract the "ID" and "Agent" columns in the Master and populate into the workshhet with the Approriate name tab. The Master list changes regularly.

View 4 Replies View Related

Count Rows In Column On Worksheet Then Insert That Many Rows On Another Worksheet

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

Conditionally Hiding Worksheets

Jun 21, 2006

I have what on the outset seemed a fairly basic task. In VBA I wanted to conditionally hide worksheets based on a value in my main worksheet.

I have 1 "main interface" worksheet with quite a few "result" worksheets as part of my workbook. Instead of scrolling left and right in the tabs menu everytime I wanted to veiw a particular worksheet I wanted to rather hide all the worksheets except the "main interface" and the relevant "result worksheet", which excel would identify based on a value in my main worksheet.

I wrote the following VBA code to do this but for some reason I cannot get it to work correctly. Perhaps I have made a simple logic error? I am fairly new to VBA. Any advice? ......

View 9 Replies View Related

Hiding Cells In Worksheet

Nov 11, 2008

I've created an Excel spreadsheet (Excel 7) to help me price sign printing jobs consistently. In my spreadsheet a few cells allow me to enter markups for materials & labour rates. These are used for calculating the retail price.

I would like to give my spreadsheet to some of my customers so they can calculate the retail price of a job themselves BUT, I would like to hide the contents of those few cells where I fix the markup. How can I achieve this?

View 3 Replies View Related

Referencing Every Nth Row From Another Worksheet

Dec 19, 2008

I need to create a worksheet whose sole purpose is to return the values from every 17th row from another worksheet in the same workbook.

I have spent a few hours trying to research the correct answer and OFFSET comes close but either I'm doing it wrong or it's not what I need.

I want to be able to drag the (every 17th row from sheet 1) formula down about 100 rows.

Assume A1 as starting place. I would prefer a formula answer as opposed to a macro or VBA.

View 13 Replies View Related

Referencing The Name Of A Worksheet

Jun 24, 2009

way to reference the name of a worksheet in a cell, or even better, use a cell reference to dictate the name of a worksheet.

In other words, I would like to create a new sheet, and set it's name ='Sheet1'!C1 so that if I changed C1, the worksheet would automatically be renamed to whatever is typed in C1.

If that is not possible, I would like to have a cell be tied to the name of a given worksheet, so that if I renamed the worksheet, the cell would change accordingly...similar to the &[Tab] function used in headers/footers.

View 13 Replies View Related

Referencing Another Worksheet

Dec 23, 2009

I have an XLS spreadsheet which updates cells through an ODBC connection to a database. The information is pulled across ok and 90% of the Vlookup and IF statements work. The problem arises on a summary page.

When i start off the summary page has cell references in numeric order to another spreadsheet and pulls back the information to 4 cells Across and 1000 Down. ='Campaign Responses - Cash Break'!A2

This is a spreadsheet that changes once the queries have run and a number between 1-1000 records are returned. However once the data has changed and the page refreshed. Not all the results have been pulled across to the summary pages. A few are there but the numeric sequence jumps from......

View 2 Replies View Related

Formatting Worksheet: Re-adjust The Worksheet To Make It More User Friendly Based On Number Of Rows In Current Sheet

Oct 14, 2008

I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved