Over 1 million tech questions and answers.

Excel: Automatically hide rows based on value

Q: Excel: Automatically hide rows based on value

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.


Preferred Solution: Excel: Automatically hide rows based on 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: 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

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
Selection.EntireColumn.Hidden = False

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

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.

[SIZE="3"]Sub Search()
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
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

Hi All,

I have had a look around this site for help and found similar posts but nothing exact or that i can get to work. I'm working on Excel 2010 and i want to do the following unhide or hide a value based on a cell value e.g. -

If Cell "C40" = Yes

Unhide Rows 42 - 47

If Cell "C40" = No or BLANK

Hide Rows 42 - 47

Any help would be much appreciated, I assume this needs to be done in VB by right clicking on the sheet>>Show Code then adding it in. Do you just save then and it should work automatically ?

Thank you

Read other answers

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

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:


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

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

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.

Read other answers

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


A:Solved: Auto hide rows based on formula results

Read other 7 answers

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

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


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.

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
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = True
End If
End Sub

Read other 2 answers

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

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

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

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

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

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

I have attached two images of an Excel worksheet. I have gone to the trouble of taking multiple sets of three columns of associated data, that is data of x, y, z where y and z are associated with x. In building a new table with six sets of data, I put x1, x2, ..., x6 in a single column, and then spaced out y1/z1, y2/z2, ..., y6/z6 in separate columns before a sort. I then sorted on values x (all x1 to x6 in a single column).

The result is shown in the first image "pre-combine-rows"

You can see that in column "all x," many rows have identical values: for example there are five rows with value x = 218.9, and six rows with value x = 224.9. I want to combine all those rows, which combines also the values in the columns y1/z1 through y6/z6.

The result should look like the second image: "post-combine-rows"

20 rows has been combined into 5 rows.

This is a walk in the park for Excel, right? Does not even need a macro, right?

Is this one of these Database/Criteria setups?

A:Excel: Combining Rows Based on An Equal Value in One Column

Hi mavigozler,

You wouldn't require a macro to complete this feat. You can achieve the wanted result with formulas. If you still need help with this issue, I'll be glad to elaborate a formula for you.


Edit: You can also have a look at the Excel matrix feature...

Read other 4 answers

Hi all,

I would like to conditionally format the attached spreadsheet so that each alternating week is shaded on whole set of rows.

Also with our roster the week runs Thurs to Wed.

I've looked quite a bit into conditional formatting now. But this one has me stumped.

Thanks for any advice.

A:Excel: Format rows based on date and a few other specifics

Read other 7 answers

Hi everybody,

I work in accounting (yeah I know loads of fun )... I have a spreadsheet that I have created to log the cash drops of several cashiers (about 140 currently) that compares it to the readings on our different point of sales systems.

this file has 31 tabs (one for each day of the month) and each cashier has the same row for his/hers information thruout the month. Each day we log how much people are dropping in cash/travelers checks, etc and compare it to what the system is telling us that they should have dropped.

Our policy is that any variance in above $10 (over or short) needs to be investigated by the manager/supervisor of the area in question and the general cashier and income audit manager should also be notified.

What I would like for excel do (not sure if it is possible) is after the general cashier logs the drops and excel tells me if each cashier has a variance above the threashold or not; to send an e-mail with the row containing the cashier's information for that day to supervisor/manager and the accounting folks involved automatically.

I am pretty novice when it comes to VBA and as much as I have been looking online and specially in this forum I haven't been able to come up with a solution to this.

Thank you in advance to anyone that can point me in the right direction.

A:EXCEL - Email rows to various addresses based on certain criteria

Well I guess nobody has any ideas

Read other 2 answers

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

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

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

Hi All,

I'm pretty good at Excel and VBA, however I'm a little stumped on a request I've had from one of my Excel users. What we want to be able to achieve, if even possible, is for particular rows to be hidden & locked unless the user has a specific password.

The table is laid out proper table format, with each row representing a record, and each column representing a field.

We want to be able to hide and lock from viewing the top, say, 5 records from view, as we need to be able to distribute the whole workbook via Email and other Web services, whilst keeping the top 5 sensitive records hidden, until a password is typed in.

Any ideas, any one.

Ps. I realise a database would make more sense, unfortunately this is unpractical, as we use WAN servers (not good for Access), and several external contractors, who need a copy of this workbook. As well as Senior Management, not wanting an Access DB.

A:Excel VBA: Hide and lock specific ROWS from viewing without Password

Read other 9 answers

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC

A:Excel Macro to copy multiple rows based on selection

Read other 11 answers


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

I have the following data that i need to create a set of rules or code that if multiple criteria is met to delete the row. I have included some examples of what i need. i tried doing a Recording macro but didnt work since sometimes some of the criteria is not there at the time of running the macro and it fails.

I need to have code look for specific values, i.e.

Account paid Y
Date of Payment Blank
Highlight all cells
'this is to review information
after this then check if it has Code 3 = C1 and Code 4=D1
Highlight all cells
'this is to review information

Check if Code 1=B2
Check if Paycode is blank or other
Highlight all cells
'this is to review information
Then delete other rows

AccountBalanceDateCode 1Code 2Due dateCode 3Code 4Code 5Account paidPaycodeJonnathan5009/1/2011A1B1C1D1E1YCollectedJessica252.359/2/2011A2B2C2D2E2YCollectedMaria321.549/3/2011A3B3C3D3E3NPayPlanLeon652.339/4/2011A4B4C4D4E4NPayPlanDavis525.329/5/2011A5B5C5D5E5YCollectedCarmen277.679/6/2011A6B6C6D6E6NOtherKarina346.869/7/2011A7B7C7D7E7NJose 677.659/8/2011A1B1C1D1E1YCollectedMary550.649/9/2011A2B2C2D2E2YPayPlanMiguel302.999/10/2011A3B3C3D3E3NPayPlanJessica372.189/11/2011A4B4C4D4E4NCollectedElaine702.979/12/2011A5B5C5D5E5YOtherPatricia575.969/13/2011A6B6C6D6E6NCollectedKaren328.319/14/2011A7B7C7D7E7NCollecteddavid397.59/15/2011A1B1C1D1E1YPayPlanJoe728.299/16/2011A2B2C2D2E2YPayPlanJoseph601.289/17/2011A3B3C3D3E3NCollectedwilliam353.639/18/2011A4B4C4D4E4Nbill422.829/19/2011... Read more

A:MS Excel 2007 - VBA or Macro to delete rows based on multiple criteria.

Read other 8 answers

Hi, I have approx 25,000 rows of data relating to parts held in inventory, on a spreadsheet in Excel 2003 SP2 .

One common cell on each line is the Supplier Stock Code and the data in it is duplicated on different rows as the same Supplier Part is held under several different Part ID's on our system, ie,

Cell A2 0-320311-00
Cell A3 0-320311-00
Cell A4 0-320311-00
Cell A5 0-330317-29
Cell A6 0-330317-29
Cell A7 0-330317-29
Cell A8 03400BK08345
Cell A9 03400BK08345
Cell A10 03400BK08345
Cell A11 03400BK08345

I have sorted the data into Supplier Stock Code order and need to find a way to automatically insert a blank row (or two) after each change, ie, in the example above it would be between rows 4 & 5, and 7 & 8.

Does anyone know of a Macro which will do this?

Many thanks,


A:Solved: Excel Macro to automatically insert rows

First things first -- welcome to the board.

2nd thing:

"insert a blank row (or two) after each change"

Hmm: for what purpose? The "official" line is "DON'T do that, it's VERY bad design". K?

So what I did was drag your last entry down to r25000, to generate 03400BK08346,
03400BK08347, and so on. "Test data".

Then I ran the code at the bottom of this post. It ground away for 12 minutes (make coffee, visit bathroom, yada yada) & then fell over on account of "ran out of worksheet" (bear in mind I had c.25K unique values, you have far fewer).

Last (but most definitely not least) thing: back up your work before trying the code.


Sub test()
Application.ScreenUpdating = False
LastVal = Range("A" & Rows.Count).End(xlUp)

Do Until LV2 = 1
x = Range("A" & Rows.Count).End(xlUp).Row
x = Range("A" & x).End(xlUp).Row

If Cells(x, 1) = LastVal Then
LV2 = 1
y = Application.Match(Cells(x, 1), Columns(1), 0)
z = WorksheetFunction.CountIf(Columns(1), Cells(x, 1))
Cells(y + z, 1).Resize(2).EntireRow.Insert
End If

Application.ScreenUpdating = True
End Sub

Read other 2 answers

Is there a way in Excel (I'm running Excel 2010, but have access to older versions) to hide/unhide cells based on hyperlinks or defined areas? I've hyperlinked so that we could go to specific parts of the document, but that does me little good because we have to hide them or the doc gets to big. Is there a way to have the hyperlink (or the cell hyperlinked) unhide the area? I've attached the document (removed all company info, but the lay out is there) so that you can see what I'm referring to.

I've thought macros, but I'm not sure how to go about it... I did define each area for the hyperlink, so would that be the route to go?

A:Excel - Hide/unhide based on defined cells/hyperlinks?

Read other 6 answers

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


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

I'm looking for a simple way to have an email sent automatically 60 days within a date on an Excel spreadsheet

Read other answers


I have multiple Excelsheets where in I use it for day today activites & tracking.
I have attached one of the simple one so that I can know the codes for sending mails & I can do it my self for the rest of the workbooks.

There is a sheet(dash board) where in all the details get updated.
When there are any changes to the value in column F, a mail should automatically sent to me giving the detials of the row. The file will be always live in the server.

I am very poor in coding & I need someone to help me in doing this.

Thanks in advance.
Ganesh Hassan

A:Solved: Automatically send email from Excel based on the conditions

Read other 8 answers

Hello everyone,

I'm trying to create a macro that will run one set of commands if someone changes a drop down menu from YES to NO, and run a different set of commands if the drop down menu is changed back from NO to YES. I've found macros that will run when a cell is changed, but I'm not sure how to write an if statement on the macro, that will run it one way or the other, based on the cells value.

The option defaults to YES. If they change it to NO, I want it to unhide the rows and enable overwriting of the unhidden cells. If they change it back to YES, I want it to automatically hide the rows and put the formulas back in. Here is the file.

Any help is greatly appreciated!

Read other answers

I have built a simple timesheet for someone in my dept, which includes amongst other things:

- hours worked over a month,
- how many hours owed/extra worked
- a cumulative of this figure over the months.

The requestor wants to be able to type a letter such as 'e' to represent a 07:30hr shift. So they type 'e' and the cell contents change to 07:30. There are about 6 different codes.

Normally a Vlookup would work EXCEPT you can't change the contents of the cell your are working upon.....cicrular referencing!

I could see two ways forward: either a function that allows the above

OR (perhaps more favourably)

leaving the lookups until the hour calculations at the base of the form. i.e Instead of a simple Sum for the hours worked that month, it could read the values in the range, look them up from a table(vlookup), and then sum the looked up figures to give the answer in the 'hours worked' cell.

I vaguely remember this being possible from my university days but can't remember how.

Can anyone help me?

A:Excel 2003: automatically changing a cell's contents based on keyword entered

Read other 9 answers


My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:


Sub Macro14()
' Macro14 Macro
ActiveSheet.ChartObjects("Chart 1").Activate
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?


A:Excel Macro to automatically change chart line style based on cell text


I'm not familiar with charts. But, see if this helps.


Read other 3 answers

I am having exactly the same problem as a closed thread. http://forums.techguy.org/business-applications/857921-excel-2007-table-filter-problems.html

Did anyone ever get to the bottom of it?

Basically, if I have a table in Excel 2007, and actually use the filter, it then breaks the autofilter.

I had a table with 6 rows, and it completely messed up.

Now I am down to 4. If I filter on 1, then 1 shows.

If I cancel the filter, only 1 to 3 show and row 4 is hidden. If I drop down the filter, row 4 does not exist.

If I unhide row 4, it is matterless. Excel 2007 's table shows it on screen, but it is no longer in the autofilter.

Filter by row 1, then row 3 disappears into hidden status, and drops off the autofilter. Rows 1, 2 and 4 now show (I unhid row 4, remember?)

BUT< the autofilter now only has row 1 in it. Rows 2 to 4 have been lost from the autofilter.

I need to be able to :
a)automatically add rows
b)filter by criteria
c)reference the table by row and column in calculations

Switching the autofilter off and on does not help. Converting it to a range means all my references went to pot, and also means that referencing and calculating sums breaks it automation. I am utterly depressed.

A:Excel 2007 table filter bug is hiding rows and losing rows from the autofilte

Ah well, Looks like I'm not the only one who had this problem. This article has a detailed explanation of what's causing the problem and a few possible workarounds: http://blog.contextures.com/archives/2010/03/19/number-the-visible-rows-in-excel-autofilter/

I found it thanks to http://answers.microsoft.com/en-us/...cel-2007/2364f944-18fa-482d-a1ac-db7464be0894

Read other 2 answers

Hi There

It has been quite a while since I asked for help. I wonder if the following is doable as an Excel macro. The attached is only a demo with only 2 fields and a few rows of data. The real worksheet has more fields and around 3,000 rows.

Sheet 1 contains the data in its initial state and sheet 2 contains data after the macro is run. You will see from sheet 2 that the field labeled QUANTITY determines the number of rows to insert and copy the correct data into those inserted rows. I want the entire row to be copied down as opposed to only the data, since I have more columns than shown here. Is this possible to do? Can anyone help me with writing a macro, since I have over 3,000 rows to do.

Thanks for all your help. You guys are fantastic.


A:Inserting specific number of rows in Excel and copying data in those inserted rows

Read other 14 answers

I do a weekly report that I base on results that I get with search done in Internet Explorer. I can then cut and paste these results into an Excel spreadsheet, but I need to delete most of the information (whole rows) and reorganize the remaining rows by taking the even numbered rows and putting them into the B column, then delete that Row as well. I have included some images that should help. A few more items:

1.)The information that I paste into the excel spredsheet may contain up to, but no more than 350 lines from the original copying source in Explorer (I'm not sure how many lines that translates into Excel)

2.) After every 20 records in Explorer there is a "top" link that gets copied, which needs to be taken into consideration when deleting the extra rows entirely

3.) Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits, as well as the rows that contain the price with the $ For instance the first record in my example I ONLY kept 8055312 11 & $70,000. The price will ALWAYS have a $ and the first set of number will ALWAYS have 7 digits first, no letters.

Attached is a jpg that shows the various steps, the last screen shot has a few of the cells highlighted in the upper left hand side. Those 6 highlighted cells is all of the information I need, and how I need it presented from the first 3 search records, the MLS number and the price.

A:Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns

Read other 16 answers


I couldn't find an answer in the Forums for this in the past half hour of looking through them- there are so many, and I need an answer sooner than in the 4 or 5 days it will take me to get through them in HOPES of finding my answer. Sorry. So could someone who knows Excel in Windows 7 help me?

I uploaded a bunch of keyword searches. Came up with 4 columns and anywhere from 100- 400 rows. I need to sort these in different ways. By Keyword so the spreadsheet is by alpha, then by Demand with highest at top/lowest at top, by Supply highest at top/lowest at top, and by Profitability highest/lowest. I know I can't do all at once, I am referring to different sorting methods.

Example: (you have to imagine 4 columns, and the numbers going under each column- when I saw the preview it bunched the numbers together and I can't seem to put them where they belong, but the first # goes under the second col (B), second # goes under "C", third # goes under "D" column:



breathing exercises 7995 5306 1507
celebrations 8523 142301 60
cervical 10235 58395 175
chakra 28455 43099 660

My problem has been that in reading the instructions I don't understand how to do this (perhaps I'm just dumb!) so that each word will still have the "righ... Read more

A:Re: EXCEL- resorting rows so they each relate to original rows

When you sort in Excel, you select all of the rows and columns of the data to be sorted. When you apply a sort to one (or many) columns, all of the data on each row is kept complete and moved up or down.

Is that what you needed to hear?

Read other 7 answers

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

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


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


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

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.


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

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:

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.


Read other 1 answers

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

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

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,


A:Deleting rows based on empty cell.

Is there anybody there? - Help!

Read other 2 answers