Over 1 million tech questions and answers.

How to hide rows in excell based on cell value

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

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.

RELEVANCY SCORE 200
Preferred Solution: How to hide rows in excell based on cell value

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: How to hide rows in excell based on cell value

Read other 8 answers
RELEVANCY SCORE 117.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 117.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 100.8

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 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 90.4

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version 1.0.0.4
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated
 

Read other answers
RELEVANCY SCORE 90.4

Dear Fellows

I am looking for help for a macro which can copy data to other sheet based on adjacent cell value which i define.

For example i have data attached in a sheet.

i want to copy data based on value present in E column to different sheets. if it is ES then all ES rows should be copied to sheet ES with roll number name father per and dept.

Same is for other departments, on different sheets. There will be 9 departments overall.

Looking for response
 

Read other answers
RELEVANCY SCORE 90.4

You must be bored with this question cos I've seen lots of them, however I can't seem to fathom out what to do..........

I've got a block of data within a spreadsheet 4 rows always in the same place.

How do I put into code:-

If column d43:e46 is blank delete rows 43 - 46 if not ignore it.

As I said I've tried a few things and it will delete the four rows regardless of them being empty of not.

Can someone help please.

Many thanks,

Moll
 

A:Deleting rows based on empty cell.

Is there anybody there? - Help!
 

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 88.8

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!
 

A:Solved: Auto hide rows based on formula results

Read other 7 answers
RELEVANCY SCORE 88.4

Hi all,

How do you ensure excel autohides blank rows that are in various formats and still shows values that do have values? (eg. $.00)

This thread has helped:

http://forums.techguy.org/business-applications/385564-solved-autohide-rows-excel.html

However, for some reason - if the cells are in $.00 format and have values in them - they are also hidden.

Thank you,
 

Read other answers
RELEVANCY SCORE 88.4

Hello to all,

I'm new to VBA in general and I'm in need of help in making my project works. I'm currently working on hidden in-sheet menu (rows 2 to 6) that could by unhidden by double-clicking on the cell "D1" and hidden again by double-clicking on "A6". I want to use that menu in every sheets.

I would really appreciate any inputs on this matter. Feel free to view my spreadsheet for a better idea.

Thanks in advance!
 

Read other answers
RELEVANCY SCORE 87.6

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 87.6

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 86

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 86

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 85.2

Hey all

New here and new to VBA so I apologize if this is the incorrect place to ask this question or if I'm not including enough information.

Basically I have two forms, one form that is a cost est. form and another form is a proposal form (client sees this one). I have the proposal form pulling information over from the cost est. form. What I would like to do is on the proposal form if rows c13:c277 are blank (no data pulled from the cost est. sheet) they will hide. But when I add data (a number) into the cost est. form the proposal form pulls that and unhides that row.

I've tried all kinds of VBA code and none seem to do what I want. I can get the rows to hide but then not unhide. I don't have code to show because I have deleted it when it didn't work. Any help would be greatly appreciated.
 

Read other answers
RELEVANCY SCORE 85.2

Hey all

New here and new to VBA so I apologize if this is the incorrect place to ask this question or if I'm not including enough information.

Basically I have two forms, one form that is a cost est. form and another form is a proposal form (client sees this one). I have the proposal form pulling information over from the cost est. form. What I would like to do is on the proposal form if rows c13:c277 are blank (no data pulled from the cost est. sheet) they will hide. But when I add data (a number) into the cost est. form the proposal form pulls that and unhides that row.

I've tried all kinds of VBA code and none seem to do what I want. I can get the rows to hide but then not unhide. I don't have code to show because I have deleted it when it didn't work. Any help would be greatly appreciated.
 

Read other answers
RELEVANCY SCORE 85.2

Hi Guys,

Apologies for repeating an old question, but I am trying to build my spreadsheet so that it auto-hides any rows were there is zero premium outstanding for a client.

I had gone through previous answers, and I had tried to lift and amend some VBA code to achieve this. However, somewhere within this code or the way I've implemented it, there is an error, because it's not showing of hiding any rows.

Can you please take a look at the attached and fix it (in order to hide all rows with a zero in column C), and maybe point out what I was doing wrong or omitting. I have anonymised my data.

Many Thanks
John
 

A:Show/Hide columns based on cell contents

Apologies... the criteria column is actually F, not C, the "outstanding premium" column. The intention being to display only those rows where client still owe premium
 

Read other 1 answers
RELEVANCY SCORE 80

Hi.

I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?

Cheers
 

Read other answers
RELEVANCY SCORE 69.2

I have a large 4000 x 25 Excell 2003 spreadsheet that keeps losing rows of data.

I normally add 5-10 rows a day and over the last few weeks I have noticed that a couple of days data has disappeared every so often. The sheet is being closed cleanly and saved. Fortunately I have a daily backup so I have been able to go back. the data is there for two days after it was originally entered and then disappears.

There has been no restore of the data and the rows have not been acidentally deleted.

Anybody got any ideas.

Thanks

A:Excell 2003 losing rows

OK - ignore the losing data, when I went to restore the missing rows I found that the section they were to go into had the row height set to zero!!! Problem is I didn't do this and i can't reset the row height using format, I am having to manually pull each row down.

Any ideas about this and sorry for the intial bun stear?

Read other 3 answers
RELEVANCY SCORE 68.4

Hello,

I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you
 

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers
RELEVANCY SCORE 68.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 68.4

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
 
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
 
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers
RELEVANCY SCORE 67.6

Hello,

I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.
Code:
Sub REMOVE()


Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row


For i = 1 To p
Range("k2").Select
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i


End Sub


 

A:Solved: Clearing Cell Contents Based off other Cell

hi
try this variation;
Sub REMOVE()

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub
 

Read other 2 answers
RELEVANCY SCORE 64.8

Hi,
I need help in appending data in a column of cells. I've been doing this manually with a cut and paste but just know that there's a better way.

Column A
line 1 4567
line 2 4568
line 3 4569
line 4 4570
line . .......

I need to add 061604 to the contents so it end up looking like this

Column A
line 1 4567061604
line 2 4568061604
line 3 4569061604
line 4 4570061604
line . .......

The contents of the cells in column A change every day but they are always 4 characters in length.
What I append changes too but is always 6 characters.

I'd like to be able to build a hot key that will let me type the 6 character data and after I type it in, automatically append it to every cell in column A.

Any help would be greatly apprciated.

Excel 2000
 

A:Excell - append data in a cell

Read other 6 answers
RELEVANCY SCORE 64.8

I can't edit the cell contents in ms office excell 2007.
please help.

A:I can't edit the cell contents in ms excell

Moving thread to the Office Forum for better results.
What does the formula say in the Formula Bar? If you Right click the Cell and choose Format Cells what is listed under the various categories (ex) Number, Currency etc. Is this only pertaining to this particular work book or is this in all work books?

Read other 1 answers
RELEVANCY SCORE 64.8

Hi guys
A Sometime ago I was highlighting some cells in excel 2003 and I was able to pick a colour in the paint box and then click on a cell in excel and the colour was pasted into the cell.
Whilst I was doing this one day something changed in the paint box and I was then able the select a default colour and then past to any cell without having to select the colour every time I pasted to a cell
The default setting requires me to select a colour every time to paste a colour into cells that are not adjacent I have to select the colour every time
The setting I accidently found enabled me to click on ant cell and the selected colour was applied.
So does anyone know how to do this in excel 2010 please
 

A:Insert a colour into a excell cell

I'm guessing you were using the format painter. On the Home tab, there should be a picture of a paintbrush. Put the color you want in one cell and then double-click (not single-click) the paintbrush. Then you can apply that same formatting to as many cells as you want by clicking in them. When you've finished, single-click the paintbrush again to turn it off. Hope that helps.
 

Read other 2 answers
RELEVANCY SCORE 64.8

is there a way to select many cells using the row and column number?
the range command permits to select knowing the letter and number of a cell, in 1 cell or many cells. range("a1:c5").select, or cells(5,12).select.

what I am trying to do is range(1,1:3,5).select. which I know it is wrong.

thanks
 

A:range vs cell selection in VB Excell

Is there any particular reason why you don't want to use the column letters in conjunction with the row number? Also, what is the purpose of selecting these multiple cells? Will you be copying the data to another worksheet? Please provide more details on what you are ultimately trying to accomplish.
We also need to know in your example above if you are only trying to select cells A1 and E3 or are if you trying to select the entire range of cells between A1 and E3 ?

Rollin
 

Read other 1 answers
RELEVANCY SCORE 64.8

I need to be able to change the colour of a cell in Excell by typing the colour name in the cell.

I have 12 colours that signify teams and I would like to type in a cell the word BLUE and the cell change to that colour without displaying the typed word.

Any idea's
 

A:Changing the cell colour in Excell

Read other 9 answers
RELEVANCY SCORE 64.8

Hi,
I have a cell that contains 1st and last names seperated by a "space". I want to seperate them (1st and last) into 2 seperate cells.

Example:
Cell A1 contents = John Smith
After seperation
Cell A1 = John SMith
Cell B1 = John
Cell C1 = Smith

Your help would be greatly appreciated! Even if it's just pointing me in the right direction. THanks. Excell 2000
 

A:Excell - seperate contents of a cell

Read other 11 answers
RELEVANCY SCORE 64.8

We have several spreadsheets that were protected by a former employee we can not get the passwords back for the cell protection that was created.
We tried save as a new name, but the cells are still protected arrrrgh

Any thoughts
 

A:Excell cell passwords lost

We cannot help with cracking passwords. It is in the forum rules. Your best bet is to try to call the former employee and try to get the password from them.
 

Read other 2 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 64

Hi, I need a formula (=if etc.) to add a leading "0" if the contents of a cell are only 4 character.

SOrry, never mind. I started this tread in error and need to figure out how to delete it.
 

Read other answers
RELEVANCY SCORE 64

Hey all,
I have been trying to get this on my own but it always comes out wrong. It should be quite easy but I just keep hitting a wall, so I am sucking up my pride and asking for help.
In Excel I use the formula today() to get a cell to display the date when I use the sheet. What is the formula for getting the time of the day to display in the same manner?
 

A:Solved: How do I get time in an Excell cell?, This should be easy but I just can't ge

Read other 6 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 63.2

My workplace currently uses an excel sheet for what are called "Daily Logs." These logs are printed approximately one month in advance, and they each are required to have the workday printed on them (i.e. Tuesday, May 1, 2012; Wednesday, May 2, 2012; etc.- excluding weekends and holidays). I was wondering if there was a way to have a dialog box to choose what days of the year you want to print the sheet for, and then, the spreadsheet would print those dates (one date per sheet).

Is this possible?
 

A:Print sequential dates represented in one Excell cell

you could use a user form with a list box that populates all the dates available in the workbook. then the use can check which ones they want printed and hit a print button
 

Read other 3 answers
RELEVANCY SCORE 62.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 62.4

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

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 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
RELEVANCY SCORE 61.6

Hi,
I have a list of 150 stores of which 20 are designated "gold" which means they get better service for deliveries. In the spreadsheet that contains these stores they are already configured to have the name of the store blocked in yellow (gold). I have to manually retype these onto another sheet, is there a way of transferring this info using the colour as the argument.

Many thanks.
 

A:copy rows into another sheet based on colour

Read other 10 answers
RELEVANCY SCORE 61.6

Good morning,

I have a spreadsheet that itemises 1000's of transactions from different customers. It grows by the day - I have a macro that deletes the records I don't want to see based on the Customer name (delete row if <> to "ABC CO." etc.). What I would like to know is is there a way of doing this without looping through every line as it's taking longer to run every day.

Hope my explanation is sufficient.

Many thanks in advance.

Moll
 

A:Delete rows based on criteria - without looping

Read other 16 answers
RELEVANCY SCORE 61.6

How can I hide rows based on the selection of a Data Validation in Excel.

Example:

If user chooses 1 from drop-down menu then hide rows 15-20 otherwise if 2 is chosen hide rows 25-35. I have a proc that will hide the rows but I don't know how to get the code to fire.

Thanks
 

A:Hiding rows based on range content

Read other 7 answers
RELEVANCY SCORE 61.6

Hi I'm absolutely struggling with this:

I have an Excel file with 2 worksheets.

In Sheet1 is a drop-down list of countries (A1), and in Sheet2 is the Data.
The Data (Sheet2) has a country field in Column A.

What I want is to ONLY display data rows in Sheet2 (or possibly a new sheet), where the country matches the country selected from the drop down entry on Sheet1.

Any Ideas?

 

A:Return Rows in one sheet based on criteria from another

Read other 7 answers