How To Hide Rows 2-5, 7-10, 12-15 Etc

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


ADVERTISEMENT

Hide Specific Rows In Sheet1 And Hide 5 More In Sheet2?

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

VBA - Button Command To Auto-Hide Rows With Value HIDE

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

Hide Blank Rows Based On Values In Other Rows

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

Hide Rows With No Value?

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

Hide Rows With VBA

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

How To Hide All Rows

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

Hide Rows If Value Zero

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

Hide All Rows With ZERO Value

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

How To Hide Rows

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

VBA For Hide Rows

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

Hide Rows If

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

Using IF To Hide Rows

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

Hide The Rows

Jan 29, 2010

I have a workbook with a number of worksheets that come in twice a week. On each sheet the layout is identical as shown below with the data for each group of cities starting at row 4 for 4 rows and then continuing for a few hundred rows without a break. The city in A4 and every 4th row is merged with the 3 rows below

Unfortunately the cities do tend to change so a recorded macro would not be reliable.

A............................. B
Birmingham.....line title
merged.......... line title
merged.......... line title
merged.......... line title
Glasgow........ line title
merged................ line title
merged................ line title
merged............... line title

What I would like is to list the cities that I am interested in in the code and for it then to hide all of the other blocks of 4 rows for cities not listed.

So using the above example if I Birmingham was not listed in the code then its 4 lines would be hidden and only Glasgow would be visiable

View 9 Replies View Related

Hide / Unhide Certain Rows

Apr 3, 2014

I highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.

View 2 Replies View Related

Select All Rows To Hide Them

Dec 22, 2008

How do I select all the Columns and Rows that are not being used so that I can hide them. I like the blue background it gives when they're all hidden. I am using Excel 2007 and it's not too bad selecting all the columns but there are just way too many rows.

View 3 Replies View Related

Hide Rows Without Data

May 22, 2009

Is there a way in 2007 to automatically hide rows that don't contain data? (I don't want to delete them, just hide them).

View 3 Replies View Related

For Statement To Hide Rows VB

Aug 22, 2009

for statement to hide rows VB. In VB, how would the following look:

View 2 Replies View Related

Hide Rows Before Printing

Sep 6, 2007

I've a worksheet that contains a whole list of items in stock.

For example
Item Quantity
hot water bottle
maggi
fab
cooking oils
breakfast oats

The above is an example of what might be seen in the spreadsheet. What i'd like to be able to do is before printing it out, i want items with 0 quantity to be shown only. So i decided to hide the rows that have items with no quantity. To do this, all i could think of is to have a button that may contain codes to hide the rows. The problem with that is the button will appear there in the printout. Is there any way of making the rows hidden before printing without using a button to trigger the code?

View 14 Replies View Related

Auto Hide The Rows

Oct 22, 2008

I want to autohide any row that does not have a number in the Current # of Bins column.

View 2 Replies View Related

Hide Rows If A Cell Value Is Zero.

Dec 23, 2008

How would I write a macro to hide a range of rows If a cell value is zero, then do the same for five additional ranges of rows?

In my words:
If AT214=0, hide rows 214 to 244
and
If AT245=0, hide rows 245 to 278
and
If AT279=0, hide rows 279 to 311
and
If AT312=0, hide rows 312 to 344
and
If AT345=0, hide rows 345 to 377
and
If AT378=0, hide rows 378 to 410

View 4 Replies View Related

How To Hide Rows That Contain Comments

Aug 14, 2009

With the help of Venkat1926 I established a macro that successfully hides a number of rows that are unrquired (based on dates):

View 14 Replies View Related

HIde Rows On Cell Value ..

Feb 5, 2010

What i want to is add some code into a worksheet where it hides rows 5:35 and shows rows 37:47 if the value of cell C3 = "Races" but shows rows 5:35 and hides rows 37:47 if the value of cell C3 s not "Races".

View 2 Replies View Related

Hide Rows When Cells Are 0

Jul 12, 2012

I would like to create a rather simple event macro. Everytime when something is selected from cell A1, which has a validation list, I would like to check the following:

Check if cells within the range B7:B10 have value zero, when they have, hide the respective row. So either hide 0, 1, 2, 3 or 4 rows.

Check if cells within the range H15:H18 have value zero, when they have, hide the respective row.

View 4 Replies View Related

Hide Rows In All Sheets?

Oct 19, 2012

I have a workbook where each sheet is password protected. I'm trying to find some code that would unprotect each sheet, and hide rows 1-12 on each sheet, and then re-protect all sheets.

View 4 Replies View Related

Hide Rows Under Cell Value

Jan 31, 2013

I would like to hide all rows under a given cell value.

For example, if Range("BJ9")=9.

I want to hide all cells in range "A10:A56".

More generally, I would like to hide all cells Range("A" & i) for i=Range("BJ9").Value To 256.

View 7 Replies View Related

Hide All Zero Valued Rows?

Jun 16, 2013

I'm trying to find a macro or conditional formatting rule that would automatically hide all rows where all cells from F to X columns in the row are '0', all I can see is how to hide it when zero appears in 1 or 2 cells only.

The spreadsheet has a drop down box, so each time when I select different item from a drop down list I wanted zeros to be hidden automatically.

View 2 Replies View Related

Hide Rows Until Certain Cell Has Value?

Jun 29, 2013

Is there a way to hide a certain amount of rows (122:136) when cell R116 has no value, but with not having to enable macros?

View 4 Replies View Related

Hide Rows In A Spreadsheet

Oct 29, 2013

I have a spreadsheet that has a range of a2 thru j62. I want to scan column B and if it is a 0, I want to hide the row and set the print area to the a2 thru j62 range and print the spreadsheet with the '0' columns hidden

View 2 Replies View Related

Macro To Hide Rows With 0

Nov 19, 2013

optimize the above Mcro to hide rows with "0".

Sub HideRowsw()

With Excel.Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

[Code]...

View 1 Replies View Related







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