I've a P&L statement having many line items. I just wanted a macro which could hide rows if for e.g. if depreciation expense is 0 in row 7 from col F throuh Col T, then I want the macro to hide it, so on and so forth.
I browsed through some macros to hide rows on this forum, but did not find them suitable for my needs.
I have a project that needs to be done today. What I'm trying to do is hide an area of a worksheet using a button click, then display that area when the button is clicked again. This area is approximately the lower right quadrant of the worksheet, so it shares column and row information with other data that must remain visible. Is there any way to do this?
Alternatively, I was thinking about making a duplicate of the worksheet, but omitting the area I want to hide on the duplicate sheet. The button would then hide and unhide the two worksheets, hopefully making it have the same effect as hiding or unhiding just that area. If I do this, I would need to maintain the exact same data in the visible area of the two sheets.
Does Excel have the ability to automatically hide rows without values in certain cells?
ABC1Route NumberTechnicianNumber of Units Cleaned25Tony237Don749 511Ray12613James16715Chuck21817
In this example, Rows 4 and 8 would automatically be hidden, leaving the other rows displaying. Of course, somehow I'd have to "Unhide" these rows at some point to add data if needed.
This is for a spreadsheet that is about 500 rows. Conceptually, I would automatically hide the rows w/o data in column B, analyze (or print) the worksheet, then "Unhide" the columns to enter data the next day.
I created a macro that analyzes some datasets and according to different parameters it puts an X at the side of every cell that goes out of parameters. These Xs are all in columns named the same way for it to be easier to find. What I want to do is to write some macro that will hide every row where the data did not go out of parameters to be able to easily see where it did. In the following example it would need to hide the second row since there are no Xs in that row. One of the problems I'm having is that the amount of columns and rows is variable.
Data Data Functional Upset Data Data Functional Upset
# # X # #
Below is the piece of code that I created to try to do this (G has the value for the amount of rows). I think it is working but it is either taking too long since it has to sometimes go through over 20k lines as much times as it needs to to cover all the rows that contain Xs or its getting stuck for some reason.
Code: Worksheets(2).Range("I3:I" & G).EntireRow.Hidden = True K = 0 Do Until Worksheets(2).Range("I1").Offset(0, K) = "" If Worksheets(2).Range("I1").Offset(0, K) = "Functional Upset" Then
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code: For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True Next cell
Code: For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete Next cell
I am building a real estate model. What I am trying to do is the following:
If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.
I have a problem with hiding and unhiding columns. Is there a way wherein I can automatically hide the row(s) if the cell = "N/A" and will unhide if it's not equal to "NA". see the attachment Im using excel 2003 btw. You can see in Sheet2 to that it contains the table for the student grades. "N/A" means that they are not enrolled on that subject. While in sheet1, What I want to happen is that you will select a student name and below, it will automatically display the grades of the student on their enrolled subjects only. So, those fields with "N/A" will hide and when I update the value of N/A it will unhide.
I have had great success using this code to hide and unhide unused space, but for some reason this morning it broke. Most likely it is my error so I am looking for another solution to resolve the issue. What i would like to be able to do it hide an entire row based on a specific cell being blank. maybe that is what this code is supposed to do but like i said it broke this morning. the cell in ref in the code is unchanged, but i did add formulas to some other cells in the same row. this is why i am looking for a way to do the same thing but based on a specific cell being blank vs what is occuring now.
I have some vba code that is used to hide any rows with the cell value of "Finished". The code is below. When I run this code I get a run time error 424 - Object Required. Can you please explain or help as to why I am getting this?
As a side point the data is loaded from an oracle database rather than from the sheet but every other function/code works fine.
Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = Sheets("Sheet2").Range("A3") Then 'A3 is the cell where your Yes/no choice is Select Case Target.Value Case "Yes": Sheets("Sheet1").Range("A7:A22").EntireRow.Hidden = True Case "No": Sheets("Sheet1").Rows("A7:A22").EntireRow.Hidden = False End Select End If End Sub
in worksheet "sheet1" I have my inputs... in "sheet2" I have my outputs... my inputs could be anything from 1 to 1000 therefore my outputs would be 1000... on the output sheet, below the last line, i have a whole bunch of text and calcs that I will always need at the bottom... what i would like to happen is if I put in "sheet1" b2 a number, say 5, then "sheet 2" would hide rows 10 to 1005 or if i put in "sheet2" b2 say 925, it hides 930 to 1005...
I created a macro, but it doesn't work at all... and i want it to work automatically without me pushing run macro...
For data validation lists B27 to B30, if any of them is equal to "EBS via ULL" then unhide rows 31 to 42. If any of the data validation list not equal to "EBS via ULL" then unhide row 44 and hide rows 31 to 42. If "EBS via ULL" and any other product in the lists is selected, unhide rows 31 to 44. If validation lists B27 to B30 are all equal to "Select Product..", then hide rows 31 to 44. I would like to use the worksheets_change event.
How do I go about hiding empty rows in a separate linked sheet? To be more precise, I'm working on 2 sheets now. Sheet 1 would be the input sheet and sheet 2, the output sheet. For instance, there are 10 rows of data in Sheet 1, but out of this 10 rows, I planted data only into 4 rows. How do I ensure that Sheet 2 hides the empty rows (5th - 10th)?
I have inserted a combo box on Sheet 1 with two options in the dropdown ('A' and 'B'). I would like to write a macro to assign to the combo box so that if the user selects 'A' from the dropdown, rows 1-3 are hidden on Sheet 2, and if 'B' is selected, rows 4-6 are hidden (with rows 1-3 unhidden). I have tried unsuccessfully to write any script that even comes close!
automatically hiding empty rows on my table... though the table has been referenced via string of formulas which I added up on my worksheet.. Some of the rows on my table are empty and I tried a VBA code on automatically deleting the rows but then I realized that if that certain 'referenced' string of that cell is not empty anymore, that would cause me much trouble.. so basically I need a code that would automatically hide the rows that displays a result of "" and unhides when it is not empty.
I have inserted images into cells, but when I hide the column the images are not hidden with that column, they just get squished between the two cells next to the hidden cell. Is there a setting or way to tie the image to that cell so that it hides/unhides appropriately with the row?
I am looking for some help with my data, I have this excel sheet in which i have certain abbreviations and answers to some questions which we use on our daily job. Now what i want to do is create a search box on the sheet and find the matching Abbrevation on the complete excel. The trick here is to hide every thing else when i search for some thing and see only for what i want to see in order to not to get confused. I am attaching the excel to give an idea to what i am looking for.
I am trying to Hide and Unhide several rows depending on if several checkboxes are marked or not.
This is the code I am using, which is effective in hiding and unhiding the rows. However, the issue I am having is: I want Row 20 to stay visible if either one of the checkboxes is marked. This string of code runs the "hide" portion if either box is unchecked.
I am trying to hide/show a static set of rows based on the value chosen within a combobox.
The combobox has a list of names and one blank. The blank is selected by default, which renders the rows hidden. When a user selects a name in the combobox the rows will be shown.
I got some help on this, which allowed me to hide the rows when the workbook was activated and when the worksheet was activated, but the code to show/hide the rows based on the combobox value is not working.