Over 1 million tech questions and answers.

Solved: Auto hide rows based on formula results

Q: Solved: Auto hide rows based on formula results

I have a sales report that shows number of leads by sales person and date. It's organized into a monthly calendar view (with dates running down instead of across) and I've used formulas so that I only need to change the year to autopopulate the dates to fall within the corresponding month. What I want to do is only show the actual business days in each month - so auto hide any Sunday or Saturday rows as well as the blank rows (just the rows that have formulas in column A, not the blank spacer rows) so that when I update for each year I don't have to manually hide the rows with no data. Can I do this with VBA code? I'm not sure if I've explained myself well, so please let me know if this doesn't make sense! I've attached my spreadsheet.

For Windows XP, Excel 2010

Thanks!

RELEVANCY SCORE 200
Preferred Solution: Solved: Auto hide rows based on formula results

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Auto hide rows based on formula results

Read other 7 answers
RELEVANCY SCORE 148

Good day everyone.
Basically I need to have a formula in excel that includes auto hide and unhide of rows based on the result value. Attached is my sample exercise for quick reference. In this exercise, I want to hide automatically the rows under "REPORT OUTPUT" that contains "0" ZERO value. Basic guide: Once you enter value (from 1 to 5) in cell D3, report output will automatically calculate...... The missing condition in the formula is to automatically hide ZERO value.... Please help.
Thanks in advance.
 

A:Solved: Formula with auto hide and unhide rows based on result value

Read other 16 answers
RELEVANCY SCORE 111.2

Hi there! I'm new to using these forums, but have spent some time scouring them from time to time to look for simple solutions to any excel/VBA problems I'm having. I consider myself relatively intermediate to advanced in Excel as far as formulas and spreadsheet creation goes, however VBA is foreign to me, and an area I'd love to learn. If any of you have recommendations on a good resource to begin learning VBA, please send it my way. And now....onto my macro question.

I've got a workbook with many tabs, and one or two input tabs feed information into another summary tab using various lookup and index formulas. There are any where from 40 to 80 rows to be qualified depending on the specific summary sheet (therefore I will likely need the macro to reference only the active sheet as it needs to work the same way on different sheets independent of each other). What I would like to do is if the result of the formula in column A of the summary sheet is "----------" (starts at A7) then I would like to hide that row, and if the data is changed on the input tab to result in anything other than the qualifying "----------", for that row to be unhidden. If this can be done dynamically that'd be great, however, if a button needs to be put into the spreadsheet to execute the macro, that will be fine too.

Thanks so much in advance for your help, and I promise I'll do my best to begin learning VBA so I can help contribute in return!
 

A:Macro to Hide/Unhide Rows Based on Results in Column Equation

HI,

Here is a code that may help you. You can copy and paste it too a module.
When you copy it to a module you can assign a "Short" cut key to activate the code.
To do this you select "Tool", "Macro" and when you see the name of the macro you want to run you can select it and then select "Options" and assign the short cut key. You will be able to use the short cut key on any active sheet.
Code:

Sub Hide_Row()
Application.ScreenUpdating = False
Dim Mysh As Worksheet
Dim cel As Range
Set Mysh = ActiveSheet
For Each cel In Mysh.Range("A1:A" & Mysh.Range("A65536").End(xlUp).Row)
If Not (Mysh.Cells(cel.Row, 1).Value Like "--*") Then
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = False
Else
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = True
End If
Next
End Sub
 

Read other 2 answers
RELEVANCY SCORE 99.6

Hi,

Is it possible to hide the entire row if the formula result is "x". (in Excel)

In my spreadsheet, I have vast numbers of results that show up as N/A. This is the correct answer, but it's making the spreadsheet hard to read because of how many there are. It's easy enough to sort afterwards, but I was hoping a a more elegant solution existed?

Thanks for any help!
 

A:Solved: Hiding rows based on formula result

Read other 13 answers
RELEVANCY SCORE 96.8

Hi there,

I have just one more component to a completed sheet, I want a code that says if cell has value highlight row

e.g. cell="SCHEDULED" highly that row from D to H, I've played around with it but no luck so far.

Thank you!
 

A:Solved: Auto Hide Rows with zero value

Hi there,

Assuming a couple of things here:

D1 is the activecell - you should see D1 in the Name box, directly left of the formula bar
A header row is in row 1, and you do NOT want that row highlighted
Each value looked at will highlight the same rows (D:H)

Follow these steps:

Select columns D:H. D1 is active.
Select the Home tab on the ribbon (if not done already)
Select Conditional Formatting
Select New Rule
Select Use a formula to determine which cells to format
In the ref edit box Format values where this formula is true enter this formula:
=(ROW()<>1)*($A1="SCHEDULED")

Please note that the formula above with the $A1 reference needs to be the column which houses the "SCHEDULED" value. So if it's in column B, that reference would be $B1 and not $A1. It is highly important the dollar sign precede the column letter but not the row number in that reference. This is what allows columns to the right of the active column to still look at column A (of that row) for the value and thus the conditional format.

HTH
 

Read other 1 answers
RELEVANCY SCORE 95.6

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

Read other 3 answers
RELEVANCY SCORE 93.6

I will eventually want this to work on all worksheets in the workbook as their data may change as the "linked tables" are refreshed. The linked tables are from Access.

What I want to do is look in column Q to see if the result of the calculation in that cell is 0. If so, I want the row hidden. I don't want to hide empty rows, just the one where the calculation =0. If the data in the workbook, i.e. the linked tables are updated, then I want any rows that have changed from 0 to another value to then become unhidden.

I have been searching for VBA code to auto hide/unhide rows in Excel 2007 based on the value in a specific column. I found the following code, but can't seem to get it to work quite right. I changed some of the criteria to fit my worksheet. The problem is that it hides all rows that are empty even though I changed the criteria from "" to 0. Also, this does not unhide rows that may refresh with a value.

I would really appreciate some guidance. My Access and Excel skills are pretty good, but when it comes to incorporating VBA into the mix, I get a little lost.

Sub HideEmptyRows()
'This macro hides all of the rows which are empty, for printing.
'created by Geoff Faulkner 12-29-2004

'Declare variables
Dim intStartRow
Dim intEndRow
Dim intTargetColumn
Dim intCounter

'set default values
intStartRow = 6
intEndRow = 2000
intTargetColumn = 17

'cycle through each row in the range
For intCounter = intStartRow To ... Read more

A:Solved: Excel 2007 Auto Hide/Unhide Rows

Read other 6 answers
RELEVANCY SCORE 90.4

Hi,
I am trying to hide rows in excel 2010 based on cell values in a certain column. I know that I have to use the VBA but I don't know how to do that kind of stuff. I know it should be simple to do but I don't know where to start. Let me know what you need in order to help me accomplish this task.
 

A:How to hide rows in excell based on cell value

Read other 8 answers
RELEVANCY SCORE 90.4

Hi All,

So I'm fairly competent in Excel in most things other than macros (which means it could be argued I know nothing about Excel...)

I'm looking for a way to automatically hide rows from a page. Essentially it's a progression tracking sheet that I've got, and I want things to stay on the sheet for only 5 days after the job is complete. I have a TODAY() function which I am using to determine whether or not it has been 5 days since completion.

Any advice would be more than appreciated and I thank you all in advance.

Tom
 

A:Excel: Automatically hide rows based on value

Bumping due to inactivity. Not sure if it's allowed but hey, I still haven't got an answer...
 

Read other 2 answers
RELEVANCY SCORE 89.6

How do I hide and show different columns based on values selected from a drop-down list? I wrote a code to do this but there has to be an easier way.

Cell C2 has a drop-down list. This list is from another part of the workbook. Next to the list in the workbook, I put values representing the beginning of the columns I want to Show.

Units 4
Intervals 5
Time 6
All 0

On the page that I am working with I have created a spreadsheet that tracks monthly amounts of Units, Intervals and Time (in separate columns). I want to be able to select "Units" from C2 and show columns, 4, 7, 10, 13... +3 each) then be able to show Intervals (and only show columns 5, 8, 11, 14... etc), then Time (and show columns 6, 9, 12, 15... etc) and if I select All, I want to show everything.

Dim x As Integer
Dim vCriteria
vCriteria = Range("C4")

If vCriteria = "0" Then
Columns("D:AS").Select
Selection.EntireColumn.Hidden = False

Else
Columns(vCriteria).Select
Selection.EntireColumn.Hidden = False

Columns(vCriteria + 3).Select
Selection.EntireColumn.Hidden = False
Columns(vCriteria + 6).Select
Selection.EntireColumn.Hidden = False
Columns(vCriteria + 9).Select
Selection.EntireColumn.Hidden = False
'... and repeat til vCriteria + 40 (column AS)

End If
End Sub

This works. But....
There has to be an easier way to write this code. Additionally, I want to be able to add more more columns to this spreadsheet as it is tracking monthly numbers so I don't wa... Read more

A:Excel VBA: Show/Hide rows based on dropdown value

Read other 8 answers
RELEVANCY SCORE 89.6

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham
 

A:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.
Code:

[SIZE="3"]Sub Search()
Columns("G:G").Select
Selection.AutoFilter
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
Operator:=xlAnd
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.
 

Read other 1 answers
RELEVANCY SCORE 89.6

Hello to evrebody,
I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.
On theattached sheet i explain what im looking for.
Pleaseo play it and hope to help me and give a solution please.
Thanks in advance.
Jose
 

Read other answers
RELEVANCY SCORE 86.4

I have a big spreadsheet of all our employees that I use tot rack meals charged at the cafeteria. I end up spending alot of time hiding and unhiding rows that have no charges. I have the names in column A and then each day to the right where they charge, I put the date. At the last column it counts across and down.

Is there a way to tell it to hide all rows with a total = 0? then I can unhide them again to see them all when I'm entering them.

Thanks
 

A:Auto Hide Rows with 0 Total?

I think the easiest way is to apply a filter to your table and then in the Total Column filter parameter un-check "0"
 

Read other 1 answers
RELEVANCY SCORE 84.4

In sheet 1, I have a list of data in A221. In sheet 2, I have formulas in the same range that ‘paste link’ the data. If, there is no data entered in any one 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? Thanks.
 

A:auto hide empty rows excel 2003

Its still me, jmannsr, trying to figure this forum out. I have an excel work book that I would love to attach or send to someone that would explain what I need help on. Any ideas how to attach an excel book here or any one willing...I can email it directly to you? Thanks!
 

Read other 3 answers
RELEVANCY SCORE 84.4

Hi! Good Day!

I cant find ways to automatically hide & unhide row depending in G14, G15 & G17. I dont know if it is applicable to Excel. Even if the solution is in other programming language, then its ok to me.

If G14=4 & G15=3 & G17=2 then hide row 28 to 29 & row 36 to 38 & row 43 to 46

I have attach a file for reference.

Thank you so much!
 

A:Auto Hide & Unhide Rows per data input

This is on the assumption that your rows are not fixed and that your code is AB#.
 

Read other 1 answers
RELEVANCY SCORE 81.6

I have a table that is set up and formatted. The cells are counted across. I would like to quickly automatically hide the rows that total to 0 - instead of highlighting each one or group of lines and hiding them.

Trying to print only the rows with totals in the easiest way possible.

Thanks - attaching the spreadsheet sans names so you can see what i'm talking about.

Carla
 

A:Auto Hide a row based on total?

Autofilter on column N for a value of 0.
 

Read other 1 answers
RELEVANCY SCORE 75.2

Hi I am trying to copy and insert rows based on a number in cell E. If the number in cell E is 4, I would like to copy 3 additional rows beneath the original and then also number the 4 rows into column F consecutively for each group. I am attaching a spreadsheet of before and after. Sheet 1 is the before and sheet 2 is the after. Can anyone help me with this. I have tried another code but it not work properly. I did find one that would put blank rows in but I cannot get past that. Any input is greatly appreciated.

Thank you

Peg
 

A:insert rows based on number in cell and copy the data down into the new rows

Hi Peg

Try this solution and see if it is ok for you. I've put in some code to help prevent the running of the code multiple times on the same sheet. If you don't need this safeguard you can delete the block of code that does this.

Also, make sure and make a backup of your file before running this - just in case.
 

Read other 1 answers
RELEVANCY SCORE 73.2

Hi,

I use Excel 2000. I would like to know if I can put a formula in a cell and then copy the result to paste elsewhere.

For example, let's say that in column A I make a list that starts with 4 in cell A1. Then A2 contains the formula =A1+2. A3 contains A3+2, and so on through A10. So I end up with the list 4, 6, 8, 10, 12, 14, 16, 18, 20, 22 in cells A1-A10.

But now, if I want to copy the result in A8 (18) and paste it elsewhere, can I do it? I don't want to copy & paste the formula because the formula will not give the correct result if the cell is not in the original sequence. I want to copy & paste 18 to a new cell, not the formula =A7+2. Can it be done? Thanks.
 

A:Solved: Is There A Way To Copy & Paste The Results of A Formula, But Not The Formula?

2 ways-

1) Copy the item and then 'paste special' (right click the cell you want to paste the number(s) in; select paste special; select values only.

or

2) Save the sheet as a .csv and all the formulas are removed.

Only two ways I've found my friend, good luck.
 

Read other 3 answers
RELEVANCY SCORE 72

I have a set of numbers that I need to compare against another set of numbers and find the values that are the same. I work with SSNs and have 1,626 people that I personally work on. Every Friday I get a set of SSNs that have new data on people some of who belong to me. All I want to do is to compare the new data with my active accounts. Before I would just use find and see if the new data contained the SSN I was working on. I have recently started actually using Excel and discovered there is an easier way.
I have column A with my costumers SSNs column B is where my formula is and column C is the SSNs I'm trying to sort to see if any belong to me. The formula I am using is
=IF(ISERROR(MATCH(A2,$C$1:$C$2272,0)),"",A2)
It worked for the first 9 rows then quit. I had to do a "find and replace" to get rid of the - (dashes) in SSNs before this would work as the data I receive just comes with the numbers.
I really hope this makes some sense to someone and they can help me out. Thanks
 

A:Solved: Formula works in first rows then quits

You can strip out the dashes using an array formula. I can't see any reason why it should stop at row 9, based on the information you've provided. It might possibly be down to spaces before or after the SSNs in either list.
If you replace your formula in B2 with this one, it will strip out any excess spaces etc and also the dashes ...
=IF(ISERROR(MATCH(SUBSTITUTE(trim(clean(A2)),"-",""),SUBSTITUTE(trim(clean($C$1:$C$2272)),"-",""),0)),"",A2)
don't hit enter, but hit CTRL-Shift-Enter to enter the formula as an array formula, which can then be copied down the column
If you have done that correctly, Excel will enclose your formula with {} brackets automatically
 

Read other 2 answers
RELEVANCY SCORE 71.6

Hi Again

CodeLexicon gave me a code that worked very well in hiding column A only. Is it possible to make the macro hide all columns with all zeros?? Also I tried to change CodeLexicon's macro to do hide all rows whose columns have zeros but without success. I tried to make the code generic.

Attached is a workbook with the 2 macros. Test the macro that I did out and see that it only hides row A only.

Mario
 

A:Solved: Macro to hide all rows whose columns have zeros

Read other 6 answers
RELEVANCY SCORE 70.8

I have a drop down list in D1 and what I need is a macro that will search for the selected value through column D and hide the rows that don't match. Think of it as like the autofilter function in excel.
 

A:Solved: Excel macro to hide rows if value does not match header

Read other 9 answers
RELEVANCY SCORE 70.4

Hi,

I have been working on a Macro in excel to take a specific criteria (cell B2, Sheet1) and search column A sheet3 for this criteria. If it is found paste that row (A-H) on sheet1 beginning at A:17. There is the possibility for 2+ rows of data with this specific criteria. I want to be able to type in cell B2 on sheet1 and have the macro lookup all rows on sheet3 and paste them beginning at A:17 on sheet1. Any help on this would be much appreciated.
 

A:Solved: Copy multiple rows in excel based on one criteria

Read other 9 answers
RELEVANCY SCORE 70

Hi, I am really hoping someone can help.

I am working on a MS Word mail merge document (whose format I do control). I need to populate a table on my document that lists the Fund #, Name and Value for up to 7 funds (3 columns and up to 7 rows). However, they do not want to have any empty rows in the table - if there are only 3 funds in the datasource, only 3 rows should show in the table. I thought there would be a way to use a bookmark to do this, but I haven't been able to figure it out.

Please help,
 

A:Solved: Show/Hide rows in a table on a Mail Merge document

Read other 16 answers
RELEVANCY SCORE 68

I have placed this line of code

Code:
Range("D49").FormulaHidden = True
in the ThisWorkbook_Event.

When I run the code, I am getting the error message "Application-defined or object-defined error".

The worksheet is not protect.

What could be causing the error to occur?
 

A:Solved: Excel 2007 Hide Formula

The data was in a merged cell.

Changed the code to

Code:
Range("D49:E49").FormulaHidden = True
The error message went a way.
 

Read other 1 answers
RELEVANCY SCORE 68

Hi guys,

Can you please help me further expand the macro below? I need it to also look between the ranges of C24 and Z24 and hide the respective column. (i.e need some sort of loop)

i.e

if C24 is 0, then hide column C
if D24 is 0, then hide column D
etc...
Sub Hide_Column ()

If Range("H24").Value = 0 Then
Columns("H").EntireColumn.Hidden = True
Else
Columns("H").EntireColumn.Hidden = False
End If

End Sub
 

A:Solved: Hide Columns based on value of cell

Read other 9 answers
RELEVANCY SCORE 66.8

Lets say I had in cell $A$1 the number "36", in row2 figures for monthly spend, in row 3 cumulative spend and in row 4 the month number(1,2,3,4..). What I want to do is substract the monthly spend from $A$1 months ago from my cululative figure in row 3 so I have a rolling cummulative hardware spend value of 36 months in this case. So in this case if I was to do it manually in cell AK3 I would have "=AJ3+AK2-A2" and drag it further right. So I add my monthly figure of spend each month to the cummulative figure in row three but also deduct the monthly spend from 3 years prior.

So in order to use the figure in $A$1 I could do something in row 3 like: starting from B3: "=if(B4>$A$1,A3+B2-[$A$1rows_further_back_thanB2],A3+B2). So when I get to month 37 in row 4 I deduct the figure in row 2 (in the formula in row 3 - the cummulative row) from 36 months prior. I hope this all makes sense. Can you help me please (see attached spreadsheet).
 

Read other answers
RELEVANCY SCORE 66.8

Hello, below I have listed a spreadsheet in Excel that represents column A-F. Column E contains formulas that come up with a number based on the data in columns B-D. Column F is just a copy of that data that I will manually adjust if needed.

I have all the rows sorted by the data in column E largest to smallest in descending order. The data in columns B-D is copied from data it finds on Sheet 1 of my workbook.

As that data on Sheet 1 updates, and the subsequent data in column E updates simultaneously, I would like rows on this sheet to automatically update it's sort according to the change in data(via formula) in column E.

I'm sure this will take macros, but I need help coming up with what to put in there. Thank you in advance!


 

A:Solved: Auto Sort Rows in Excel

Read other 7 answers
RELEVANCY SCORE 66.4

Is there a way to protect a cell so that its content cannot be changed/moved.

Also, for the same cell, which has a formula, is there a way to hide the formula?

Thanks
 

A:Solved: Excel 2007 - Hide formula, protect cells

Select the cell (cells) you wanna protect, rgiht click, Format cells, go to the protection tab, and check both looked and hidded, click OK
After that go to Review Tab, and select Protect Sheet, Type a password if you wanna and save file
Close and reopen it!
 

Read other 3 answers
RELEVANCY SCORE 66.4

I set the Taskbar tab (in Taskbar & Start menu Properties) to Auto-hide the Taskbar. When I select Apply, the taskbar dips and disappears for a second and then comes right back and stays there.

Is there a way to make the auto-hide work?

W7 Home 6.1 / 7600

Thank you

A:Auto-hide Taskbar Fails to Auto-hide

HI! You can use this nifty 3rd party free software to hide the taskbar and re-enable it when you need to! On a personal note i find the taskbar in Win.7 a wonder! Different strokes for different folks,i guess!

Download ShellWin: Partially Hide/Show Windows 7 Taskbar With ShellWin

regards,
sreedhav

Read other 5 answers
RELEVANCY SCORE 66.4

Hi

I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?

Thanks

Garry
 

A:Solved: Excel Pivot Table (based on query with formula)

Read other 16 answers
RELEVANCY SCORE 64.8

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.
 

A:Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers
RELEVANCY SCORE 64.8

For some reason I cannot get Excel 2000 to display the results of a formula. Only the formula text displays. I've gone to Tools-Options-View-Windows Options and checked and unchecked the Formulas checkbox. It doesn't make any difference.

Help!

Thanks,

Thomas
 

A:Excel 2000 Formula Text Displays instead of Formula Results

Format the cell as a number, it prolly is fmt'd as text
 

Read other 3 answers
RELEVANCY SCORE 64

Is it possible to hide rows when using conditional formatting. What I want to do is when I type “complete” in a cell I want corresponding row automatically hide itself. Is this possible?
 

A:Hide Rows

No. But you can apply a filter. Data-->Filter-->Autofilter and choose BLANKS to be what you want to see. That should hide the Complete ones. I did not test if it will do it "on the fly".
 

Read other 3 answers
RELEVANCY SCORE 64

Hi all,

Here's my problem...

I have written a nice spreadsheet for writing proposals for work. I would like to create two macros.

"shrink list" and "expand list"

I have set all cells in column A to be a value based on column B. Once I have completed my proposal, I would like to click "shrink list" to hide all ROWS that have "0" (zero) as it's value. And then be able to click "expand list" to show all rows again. Essentially, the macro will look only at column A and hide all of the rows that show a zero.

I know that I can do this with autofilter, but this needs to be simple for less knowledgeable users.

Can someone help? I will donate if this complex (for me) function is made simple.

Thank you!!!
 

A:Hide ROWS with zero as value

Sub Shrink()
Application.ScreenUpdating = False
For Each Cell In Range("A1:A100")
If Cell.Value = 0 Then
Cell.EntireRow.Hidden = True
End If
Next Cell
Application.ScreenUpdating = True
End Sub
'
Sub Expand()
Application.ScreenUpdating = False
For Each Cell In Range("A1:A100")
Cell.EntireRow.Hidden = False
Next Cell
Application.ScreenUpdating = True
End Sub

Edit the line 2 range to what you need.
 

Read other 2 answers
RELEVANCY SCORE 63.2

Hi,

I need a little help with a VBA code: when a radio button is clicked, then a number of rows on an other sheet are hidden. The rows which are hidden contain a certain text string.

Thank you in advance.
 

A:VBA conditionally hide rows

Hi,

Thank you for the reply.
Here's what I'm trying to do: I have a series of buttons on Sheet1 and a series of questions on Sheet2. If I press Button1 (named "John") on the first sheet then I'd like that all questions on the second sheet that contain the "John" reference to be hidden. The questions that contain the "John" reference are not contiguous, but are spread throughout the second sheet.
Could it be possible to hide the rows based on the keyword? For example: if I press the "John" button then the VBA macro searches and hides all rows that contain the "John" reference.

Thank you in advance.
 

Read other 3 answers
RELEVANCY SCORE 62.8

Hi,

based on this...
http://forums.techguy.org/business-applications/910811-calculated-values-charts-s-store.html
​...I will be storing values calculated on a few formulas in the DB, I wonder if thers is some sort of recommended way to do it?

So far my approach would be like this:

Ctl A - input field using table field as 'data source'
Ctl B - input field using table field as 'data source'
Ctl C - unbound field with formula to calculate and show on screen the Resulting Value.
Ctl D - "print" field where Results are 'echoed' so they get stored in the bound table field (using table field as 'data source')

I am using C and D because the source of C is a formula instead of a table field and therefore it can't be stored.
Maybe there is a way to avoid this and I should do that instead?

TIA.
HQ.
 

A:Solved: Store formula results in Access 2003

Read other 13 answers
RELEVANCY SCORE 62.8

Trying to sum cells that contain results from formulas. The only thing I've found that works is replacing the formulas with their calculated values using "copy," "paste special" "values" "add."

My spreadsheet contains formula results based on selections from combo boxes on another worksheet. It calculates material amounts based on design selections. I would like to be able to "Sum" each row (material types) when quantities vary based on conditions (columns.)

Would like a reusable spreadsheet that I didn't have to overwrite in order to get the totals.

Any suggestions would be greatly appreciated.
 

A:Solved: Summing cells that contain formula results in Excel?

Read other 7 answers
RELEVANCY SCORE 62.8

I have spreadsheet that has a bunch of parts listed in separate categories. Column A is the quantity column. I want to be able to hide all rows (using a macro) that have a zero in column A. The workbook has several sheets that need the same functionality. The macro must not hide rows with nothing in them or nothing in column A. For example a row may have content in other columns but nothing in column A because that row is not used for a specific part (therefore there will never be a quantity entered).

The code below works great except that it hides all rows with that are zero or blank. I just need to figure out how to make it skip the blank rows (column A has no data). Any ideas? It doesn't have to be with my code just something that provides this function for all sheets in the workbook. Thanks.

Option Explicit
Sub HideRowsWithZeros()
Dim ws As Worksheet
Dim c As Range
Dim rngRange As Range

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

ws.Select
Set rngRange = Range(Cells(1, 1), Cells(65336, 1).End(xlUp))

For Each c In rngRange
If c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next c

Next ws

Application.ScreenUpdating = True

End Sub
 

A:Excel Macro to hide rows

Read other 6 answers
RELEVANCY SCORE 62.4

The Taskbar's auto-hide function rarely works regardless of where on the screen it is positioned. It just stays up and other full-screen windows do not compensate/resize so they have a portion chopped off.

Is this a known error?

Windows XP Pro 2600 sp2

Thanks
 

A:Solved: Auto-hide Taskbar very unreliable

how often do you reboot? i shutdown at the end of the day.
 

Read other 3 answers
RELEVANCY SCORE 62.4

Hi guys, i'm new to this forum. Bomb #21 posted a great formula to a 2009 question regarding creating a formula to compare two rows. http://forums.techguy.org/business-applications/811590-excel-formula-compare-two-rows.html and it's been really useful, however, every once in a while the formula simply doesn't work. By all accounts i attempt to copy all the settings of a file where the formula works for me (i.e. make sure the value format is consistent). The formula just returns the name from cell "B" even though there is a match in column "A". To recap, Bomb #21 posted the following:

" ... list out names in column C that are in column B and is missing in column A"

Use this in column C as far down as you have values in column B:

=IF(ISNA(MATCH(B1,A:A,0)),B1,1)

to return either the name from B or the value 1.

Then select all of column C, press F5, click Special. Select Formulas, UNcheck Numbers, click OK. Hit CTRL+C to copy the names, select D1, then Edit > Paste Special > Values > OK.

Delete column C to top & tail. HTH
Your help would be much appreciated!
 

A:Excel Formula to compare two rows

"The formula just returns the name from cell "B" even though there is a match in column "A"."

Check "offending" column A values for trailing spaces.
 

Read other 2 answers
RELEVANCY SCORE 62.4

Hi, I am preparing a template to pass on to a number of people to use - some with lets say "limited" computer skills (eg still struggle to open, edit, reply etc to emails!). I am therefore hoping this document will do everything possible automatically.

They will (or have done for them) copy and paste their data into sheet1 (Overview) my spreadsheet has 3 additional sheets (Comments, No Comments and No shows and cancelations).

Sheet1 (Overview) Column A will only ever contain the words; Attended, Cancelled or No Show. And Column B; yes, no {or left} blank.

My formulas take all the rows with Attended & Yes into Sheet2 (comments), Attended & No into Sheet3 (no comments) and No show or Cancelled into Sheet3( No sho..etc).

=IF(Overview!$B2="yes",Overview!A2,"") - Formula used in Sheet2 (Comments)
=IF(Overview!$B2="no",Overview!A2,"") - Formula used in Sheet3 (no comments)
=IF(Overview!$A2="no show",Overview!A2,IF(Overview!$A2="cancelled",Overview!A2,"")) - Formula used in Sheet4 (No sho…etc)

These work well in getting the correct data into each sheet, however the data shows up in the same position/rows as they begin on Sheet1, meaning there are blank rows.

Here we go… What I would like is either an additional formula or if better an alteration to my current formulas (and please not a Macro {IT policy issues} ) which automatically removes the blank rows or moves the non blank rows to th... Read more

A:How to use a formula to remove blank rows

KPC123,

Welcome to the forum.
See if this site helps you.

http://www.cpearson.com/excel/NoBlanks.aspx
 

Read other 2 answers
RELEVANCY SCORE 62.4

Hi all,

I have a excel sheet which has two columns A and B. Both the column contains names and column B has more names than in column A. I am looking for a excel formula that can compare names in A and B and list out names in column C that are in column B and is missing in column A

Help will me much appreciated.
 

A:Excel Formula to compare two rows

Read other 7 answers
RELEVANCY SCORE 62.4

Dear Team,

There is an excel where i need to transpose account number which are in particular cell (rows) to columns.
Is there any macro or formula to transpose row data into columns.
Attached excel.

Thanks in advance for your help.

Regards,
Rashi G
 

Read other answers
RELEVANCY SCORE 62.4

I have Windows10 with dual monitors. When I start Windows the taskbar on Monitor 2 is visible and won't move. If I turn Cortana on and off it resumes auto-hiding but I have to do this every time I start Windows 10.

Any ideas?

A:Auto-Hide doesn't hide taskbar

This behaviour has been happening for a long time now. I don't think MS are giving it much priority at the moment. The search on & off method seems to be the main workaround everyone is using.

Read other 1 answers
RELEVANCY SCORE 62.4

I have Windows10 with dual monitors. When I start Windows the taskbar on Monitor 2 is visible and won't move. If I turn Cortana on and off it resumes auto-hiding but I have to do this every time I start Windows 10.

Any ideas?

A:Auto-Hide doesn't hide taskbar

This behaviour has been happening for a long time now. I don't think MS are giving it much priority at the moment. The search on & off method seems to be the main workaround everyone is using.

Read other 0 answers
RELEVANCY SCORE 62

I am building an excel model and want to link a macro to the "calculate" button based on the above check boxes. If the boxes are selected, the relevant graphs would need to unhide and show and then hide as the boxes are deselected again.

I've attached a screen shot of what I'm working on.

I would appreciate any help possible on how to write the code for this macro.

Many thanks
 

A:Excel 07 - Macro to hide/unhide rows

Welcome to the board.

Screenshots saved as bitmaps are huge. Convert them to (e.g.) jpgs, then you can upload without zipping.

Re this part of your post specifically:

Macro to hide/unhide rows

, one "basic" answer is:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Rows("11:20").EntireRow.Hidden = False
Else
Rows("11:20").EntireRow.Hidden = True
End If
End Sub

Do you actually have ten charts, or is it far more in reality?
 

Read other 2 answers