Automatically Hide / Show Rows Based On Cell Value
I wrote a small code to hide some columns if a certain cell is equal to a certain string.
The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.
Sub Auto_Open()
Range("A1").OnEntry = "Action"
End Sub
Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Automatically Hide & Show Rows Based On COUNTIF Result
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns and i need the macro to be fast Sheet1 If the value in Range BE11:BE160 equals 1 don’t hide the row If the value is “0” or “ “ then hide the row Sheet2 If the value in Range BE11:BE160 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row Sheet3 If the value in Range BE11:BE160 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row Sheet4 If the value in Range O1:O150 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row Sheet4 If the value in Range B1:B150 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row
View Replies!
View Related
Hide & Show Rows Based On Cell Value
I have two sheets say: Sheet1 Sheet2 Sheet1 has a few comboboxes saying (YES / NO) conditions Which are assigned to particular cells (for Ex: say Combobox1 value assignes to Sheet1!B5 ) If Sheet!B5 = YES some rows in Sheet2 Say ( Row12 ,Row 15,Row 16) has to be hide. I will add a command button to sheet1 and call macro if i click command button checking the conditions in sheet1 combo boxes..rows in sheets2 has to hide..
View Replies!
View Related
Automatically Hide/Show Rows Across Worksheets
When I call a series of subroutines from different worksheets, ScreenUpdating = False is not working.Here is my Private Sub Worksheet_Change(ByVal Target As Range) With Application .Calculation = xlManual .EnableEvents = False End With If ActiveCell = "No" Then Call Sheet3.DisableTBs Call Sheet4.DisableTBs Call Sheet5.DisableTBs Call Sheet6.DisableTBs Call Sheet8.DisableTBs Call Sheet9.DisableTBs Call Sheet10.DisableTBs......................
View Replies!
View Related
Automatically Hide Rows Based On A Value In Column Q
After the user selects something using the validation list in cell F38, the macro should check in column Q in the rows 44 till 425 for "hide". For each row in which it finds "hide" the entire row should be hidden. I have the code below but it does't work yet. It calculates for ages and afterwards only the first 2 rows of the 381 are hidden (probably because the third row is not "hide"). Private Sub Worksheet_Change(ByVal Target As Range) Dim MyRange As Range Dim ThisCell As Range Target = Range("F38") Set MyRange = Range("Q44:Q" & Range("Q425").End(xlUp).Row) For Each ThisCell In MyRange If ThisCell.Value = "hide" Then ThisCell.EntireRow.Hidden = True End If Next ThisCell End Sub
View Replies!
View Related
Toggle To Hide & Show Rows Based On Conditions
First, I realize there are plenty of hide cells threads but I have yet to find one pertaining to my situation and I apologize in advance if I this solution has already been posted. The Problem: I am looking for macro code to a toggle button that will hide various rows that have no value between multiple sets of rows. The toggle should refresh the format of the rows as the information that was blank could later on have value. The Setup: For each set of rows, the first row will have the label T and the last will have the label S. In between T and S there can be data. If the cells between T and S are all empty then the rows including T and S should be hidden other wise only the non blank cells between T and S should be visible. The Reason: I have a master database worksheet, there are four copies of the master database worksheet each entitled phase1, ""2, ""3, & ""4 respectively. These phase sheets are linked to the master sheet and show the entire row's data based upon the beginning cell of each row showing either phase1, ""2, & so on.. The data is broken up into many sub databases and traditional auto filters or advanced auto filters will not be applicable as the title of the data and the empty rows in between need to be hidden if the data is empty.
View Replies!
View Related
Hide & Show Rows Based On Multiple Conditional Formatting
I am trying to hide/show entire rows of a range based on the conditional formatting in the row. I want all rows with at least one overdue training cell (indicated by a red cell) displayed, and rows with no overdue training hidden. The conditional formatting formulas vary greatly, but always result in a white (unchanged), yellow, or red cell. Here is a sample picture for reference: [url] The CF formulas vary based mostly on two major factors: the frequency of the requirement found in Column "C" (Monthly, Quarterly, Semi-Annual, or Annual Requirement), and the personnel's arrival on site or date of departure (wheels up) found in Rows("3:4"). Each training class has two rows. The first row indicates the last time the class was completed, and the second row shows when it is due next. Both rows have to be displayed/hidden based on the second row's conditional formatting. Here is the code I am using right now: ...
View Replies!
View Related
Macro Code Automatically To Hide Rows Based Condition
I have a spreadsheet that calculates percentages and then outputs the results to a pie chart. There are 9 different percentages being graphed in cells A41 to A49. The chart looks weird if any of the percentages end up being 0, so I have the formula set to add 0.00001 to each calculation (so they show up as 0% and display on the chart as 0%, but truly are 0.00001). I would like it so that if any of these 9 percentages ends up being 0 (or really 0.00001) that the row automatically hides and thus won't display on the pie chart. How can I create a macro that automatically runs to accomplish this, and automatically updates as percentages are recalculated.
View Replies!
View Related
Hide Or Show An Image Based On A Cell Value
I have a spreadsheet with two bitmap images inserted into it. In cell E2, I will enter a number, either a 1 or a 2. if I enter a 1, I want only the first image to be visible. If I enter a 2, I only want the second image to be visible. Is there a way to accomplish this (hopefully without the need for macros)? I've attached a spreadsheet as an example of what I'm trying to do. Also, note that I'd like the images to be stacked on top of each other so that they show up in the same place regardless of wether there's a 1 or a 2 in cell E5
View Replies!
View Related
Automatically Hide/Unhide Columns Based On Cell Text
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 13 Then Exit Sub If InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then Columns("N").Hidden = True End If End Sub but I have a lot of columns that I need to perform as above and I have put the code together as below Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then Columns("N").Hidden = False ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then................. Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
View Replies!
View Related
Hide/Show Sheets Based On Cell Values & Validate Entry
excal VBA programming.I have attached the file name "help" for your easy explanation purpose. 1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell. 2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
View Replies!
View Related
Hide & Show Sheets Automatically
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden. This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ws As Worksheet Cancel = True Application.EnableEvents = False Sheets("Protected Content").Visible = True For Each ws In Worksheets If ws. Name <> "Protected Content" Then ws.Visible = False Next ws Me.Save For Each ws In Worksheets ws.Visible = True Next ws Sheets("Protected Content").Visible = False Application.EnableEvents = True End Sub
View Replies!
View Related
How To Hide/unhide Rows Based On Cell Value
I'm using column A as a reference. My data starts in cell A3. Whenever theres a zero in any cell in column A I would like that row to hide. If the row was hidden and the value changes to any number greater than zero I need the row to unhide. I can record two macros for the hide/unhide part of this but I dont understand how to run them based on cell value.
View Replies!
View Related
Hide Rows Based On Cell Value Being Empty
I have read other posts about this but I am still a little confused as most macros that have been made are tweaked to suite the users individual needs. What I want is something like this to work in the active sheet: If cells D2:D55 = "" Then Hide.EntireRow If cells D2:D55 = "has any value" Then Show.EntireRow The values in D2:D55 are populated by a VLOOKUP depending on what someone chooses in a drop down validation list, however not all the rows are always required so I would like to hide them to save some space on my form.
View Replies!
View Related
Auto Hide Rows Based On Cell Above
I've attached a screenshot to illustate what I am doing and a one tab version of the workbook. I've had to do it in a zip folder as they were too big individually. I've got a table that starts at row 12 and finishes at row 217 and the users enter information into the rows over a year. The creator of the sheet has set it up so there is a "z" in the second cell of each row and as this cell is overtyped with the new information the row changes colour and is included in the selected print macro that is set up. I want to add in a macro that changes the row height to 0 based on the "z" being present in the row above 2nd cell. So all that is showing in the table are the rows that have info in them and one blank one underneath. So everytime a new row of info is entered either a new line will reveal itself underneath or there is a control button on the sheet that the user can press to reveal a new empty line. I don't know how to write VB, but I've found some code online that claims to do what I need, but I need it to be altered to use the presence of the "z" in the row above (2nd column) as the trigger for the rule: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Value = 1 Then Rows(Target.Row).RowHeight = 0 Else End If End Sub Or should I be starting off with minimised rows and changing it so the height increases as the "z" in the row above is overtyped? Can this happen automatically as the z is overtyped or does the macro need to be triggered by a control button for example? Is there a better way to do this? I don't want to get rid of all the extra empty rows and have a macro to create a new row for 2 reasons: 1.They have formulas and macros running set up by the creator that I don't want to mess with and 2. There are 52 sheets in the workbook, 1 for each week of the year and the next sheet takes the information from the previous weeks sheet so on the last sheet, number 52, it has every line that has been entered over the year from week one to week 51 carried over. If I created a new row on week2, I would have to then create that row on every sheet following week 2 and I think that would make it more complicated. I would need the macro to be able to run on any of the 52 sheets. Private Sub Worksheet_Change(ByVal Target As Range) Cells.RowHeight = 12.27 Range(Rows(Target.Row + 2), Rows(217)).RowHeight = 0 End Sub
View Replies!
View Related
Hide/Unhide Rows Based On 1 Cell
I'm trying to find a way to hide rows based on a condition in one cell. Basically I have a pull-down list with Yes or No in it. I want to hide rows 52 through 57, if No is selected, and make the rows reappear if Yes is selected.
View Replies!
View Related
Conditionally Hide Rows Based On Cell Value
I'd like to hide rows based on the value of a specific cell (that specific cell will always be C14, and it will have up to 8 variables selectable from a drop down list). For example, if C14 = Innovative/exploratory, i'd like to hide rows 15-45. If C14 = Technical Service, i'd like to hide rows 15-32, and 42-44. I'm not familiar with VB, but am a quick study -
View Replies!
View Related
Hide Or Filter Rows Based On Cell Value
I need to hide rows based on a cell value. In cell B4 I list how many students are in period one. I have enough rows for 35 students found in A10:A44. In C10: C44 I listed numbers 1-35. So I need any row that has a number in the C column higher than what was entered in B4 to be hidden, but when cell B4 changes I need rows to unhide if their row is less than new value. So here was my attempt: ...
View Replies!
View Related
Hide Rows Based On Other Cell Values
I need to hide rows on a worksheet based on entries from DATA VALIDATION dropdown lists on another. Due to the fact that all the entries are on a different page, and there is no active cells or entries made on the "second" worksheet, the WORKSHEET_CHANGE events will NOT work. Trust me, I have spent two days searching the net for a possible answer and trying every trick I could find. Not to seem redundant or a pain, but right from the git-go, FORGET the WORKSHEET_CHANGE event as an option. I need a way to fire the macros from a formula change in a linked cell, and it won't be an active cell, nor will there be any kind of entry made on that page at all. ALl the entries (choices) will be made on another sheet, but the result needs to be hiding the rows not required based on the choices I make. There are over 300 lines and when I am done there will be only approximately 35-40 left. Right now the people using this are actually making copies of the workbook, and then deleting the worksheets they don't need (I am hiding those with an array) and then deleting the lines they don't need on the worksheet remaining. (Stupid and slow I know, but I didn't develop this workbook, I'm just trying to bring it out of the stone age) Here is some sample code with what I want to do, except it is of a WORKSHEET_CHANGE nature and will not work. Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Range("INPUT!M6"), Target) Is Nothing Then Rows("11:50").Hidden = True If LCase(Range("INPUT!M6").Value) = "100" Then Rows("31:50").Hidden = True If LCase(Range("INPUT!M6").Value) = "100" Then Rows("11:30").Hidden = False If LCase(Range("INPUT!M6").Value) = "225" Then Rows("11:30").Hidden = True If LCase(Range("INPUT!M6").Value) = "225" Then Rows("31:50").Hidden = False If LCase(Range("INPUT!M6").Value) = "0" Then Rows("11:50").Hidden = False End If End Sub
View Replies!
View Related
Hide Rows Based On Value In Specific Cell
Cell A1 can be left blank or have any value 1 to 99999 inserted by the user. Default value is null. My data starts on row 5 and currently goes to row 62, but will include more rows over time. I want to automatically hide rows 5 to 62 (and higher later on) where the value in Column C (C5, C6, C7, etc) is less than or equal to the value in A1 as long as A1 isn't null. If A1 is null, then no row hiding would be done. Ideally would be great to have msg also that says "No rows hidden" or "10 rows hidden", but not critical - just nice to have if you can provide such easily and quickly otherwise I'll just try to learn it later on.
View Replies!
View Related
Hide & Unhide Rows Based On Cell Value
I realize there are many Hide/Unhide requests. Although, believe me when I state that I have checked into the matter, and tried to solve it on my own. If any of you fine forum dwellers could point me in the right direction, I would greatly appreciate it. I am trying to achieve the following: Two Macros: #1: To hide rows(entire spreadsheet) based on cell value in Column C (value is '0') Here is a code (from this forum) that seems to be the least complex/confusing. I don't understand the "AC2" value. Would it not be A2? Or does it signify a certain range? Sub HideRows() With Sheets("Store Snapshot") If Range("AC2").Value = 2 Then Rows("13:15").EntireRow.Hidden = True If Range("AC2").Value = 1 Then Rows("13:15").EntireRow.Hidden = False End With End Sub #2: To UNHIDE the rows that Macro #1 Hid.
View Replies!
View Related
Macro To Hide Rows Based On Cell Value (compared To Other)
I want a macro to hid rows based on a specific value of a cell compared to another. Example, In a cell I have the current date and time which updates when the the sheet is opened... On a row I have various info including another date in the past. When the date in the past goes over 6 months older than the current date, I want to hide that whole row. Is this possible? Do I use an IF function in the macro I've seen to remove rows based on cells with specific values?
View Replies!
View Related
Automatically Color Rows Based On Cell Value
I currently use spreadsheets to report on backup processes. I am trying to speed up this process and one thing that woudl help me is a function that will automatically fill the row with a colour depending on the choice in the dropdown box, so if you choose 'successful' from the dropdown list the row colour would fill to green and if you select 'failed', the row would change to red and a choice of 'completed/errors' would result in orange. This process is currently done manually and often results in errors like a succesful result with a red filling. Is there anyway to automate this process? i was trying to whip up a macro, but have been unsuccessful so far.
View Replies!
View Related
Show Hide Rows On Click
if it is possible to hide the contents of a column that would only show once clicked on? That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
View Replies!
View Related
Dynamically Hide/show Rows
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
View Replies!
View Related
Hide Show Blank Rows
I have a lengthy column containing text information. Within this column are various gaps. ie: several cells with no information. eg: ..... Row F 1...text info 2...text info 3 4 5...text info 6...text info 7 8...text info I would like to create a simple macro (switched via toggle switch) whereby it hides/unhides the rows containing cells with no text information. It should also be mentioned that this column contains various background color formatting, for both empty and text cells.
View Replies!
View Related
ToggleButtons For Hide And Show Rows
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using. Private Sub ToggleButton1_Click() With ToggleButton1 .Caption = "Goal 1" End With Dim rCell As Range If ToggleButton1.Value = True Then For Each rCell In Range("P2:P99") rCell.EntireRow.Hidden = rCell > 1 Next rCell 3) Else Range("P2:P99").EntireRow.Hidden = False End If End Sub I have three questions - 1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3. 2) Is this code an efficient way of doing what I want? 3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.
View Replies!
View Related
Automatically Hide Blank Rows In Spreadsheet
I have a spreadsheet that is linked to another spreadsheet in a workbook. The information comes from an export of an access query into a template in excel that I am using just to store the values, then I link the values to the appropriate field in another sheet. I was wondering is there a way to programmatically hide blank rows in this sheet starting at a specific row of the page.
View Replies!
View Related
Hide & Show Sheets Based On Names
I made one file with 13 sheets. sheet1 tab name is : MAIN and other sheet tab name like following 2. xyz-Sales 3. xyz-Rev 4. xyz-SSN 5. xyz-ddn 6. abc-Sales 7. abc-Rev 8. abc-ddn 9. abc-ssn 10. ddd-sales 11. ddd-Rev 12. ddd-ssn 13. ddd-ddn In Main sheet There are 3 buttons 1 . XYZ 2. abc 3. ddd when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide i don't want to use Sheet2.Visible = xlSheetVeryHidden i want to use finde xyz sheet tab name and shows and other are hide.
View Replies!
View Related
Show/Hide Command Button Based On Location
What i am trying to do is make a command button appear when you hit any cell of the row its on. For example, If you were to hit any cell on rows 1 or 2 the command button will appear and when I am not on the visible property goes back to false. I have a ton of buttons on this sheet and I am trying to clean it up so buttons only appear as needed.
View Replies!
View Related
Hide/Show UserForms Based On Workbook And Worksheet
I'm just beginning to work with userforms and have created a couple of forms for workbook navigation. The Menu form is set to appear only on the menu worksheet and the Navbar form is set to show on several database sheets. Both forms are set th hide when the workbook is deactivated. Private Sub Workbook_Deactivate() Menufrm.Hide Navbar.Hide End Sub The problem is that when I return to the workbook the forms do not unhide. Using the workbook_activate event causes both forms to show simultaneously. I also have the worksheet_activate event set to show the applicable form. Private Sub Worksheet_Activate() Application. ScreenUpdating = False ActiveWindow.DisplayGridlines = False Navbar.Show Navbar.Left = Range("B3").Left Navbar.Top = Range("B3").Top Application.ScreenUpdating = True End Sub Upon returning to this workbook, I only want the appropriate form to show relative to the worksheet that is active. That is, if I was on sheet2 when I switched to another workbook, sheet2 would be active when I return to the workbook and should show the Navbar form. The menu form should show on sheet1, and the navbar form on sheets 2-5. How do I get the correct form to show when I re-activate the workbook?
View Replies!
View Related
Macro To Automatically Delete Data And Hide Rows
I have a spreadsheet that allows room for 35 students per period, but instead of having the teacher manually go in and delete the extra data and hide the rows, I want to create a macro that will do so. In cell B4 the teacher will enter how many students are in their first period class. (cell C4 for second period, cell D4 for third etc...) I want the entering of the number to automatically hide the superfluous rows and delete the data in the second column for those rows. I don't want the rows to be completely deleted because another year they may have more students and need those rows back. I have attached one of the workbooks that I need to put this macro into. I have created room for 35 students in a given period. So if they have 23 students entered into B4 (period one) I would need rows 30-41 to be hidden, and I would need the formulas in B30:B41, E30:E41, H30:H31, K30:K31 etc....deleted. Basically the point of the formulas is the teacher will enter the total points possible on that given assignment in cell B6, E6, H6 etc....and it autofills that score down, so the teacher only enters those that missed points instead of entering in the missed ones and the 100 percent ones. I would need those formulas to be deleted because if the assignment was out of 10 then cell B30-B41 will give students a 10, and then the class average will be computing those scores,but those students don't exist. But if I can't get the macro to auto delete the formulas, I will just not have those formulas in there, and the teacher can enter all scores.
View Replies!
View Related
Hide/Show Pivot Table Data Based On Criteria
I want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging: Sub FilterDates() Application. ScreenUpdating = False Sheets("PnL").Select Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _ .PivotItems("01/01/1950").Visible = True 'to always have 1 populated
View Replies!
View Related
Hide Show Pivot Column Items Based On Date
I have a pivot table with the column fields as dates. I will like to be able to use VB to dictate which value to show. Here is brief code in which I just recorded... Sub Button1_Click() Range("B7").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date") .PivotItems("10/30/2006").Visible = False .PivotItems("10/31/2006").Visible = True End With End Sub I would like to show current day only using =today() or something similar. THis would eliminate the user having to modify the pivot tables daily.
View Replies!
View Related
Hide/Show Pivot Table Data Based On Date Criteria
I have looked at the below Hide/Show Pivot Table Field Items help web pages: Hide/Show Pivot Table Field Items Hide Pivot Table Fields Pivot Items by Criteria I am trying to use the above, but with dates in the following format in each cell: YYYYMM 200612 200701 200702 200703 200704 200705 200706 etc, etc The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt. Sub HideByCriteriaYYYYMM() 'Declare variables 'SEE: [url] 'SEE: [url] Dim pt As PivotTable, pi As PivotItem Dim lMonth As Long Dim strCri As String, strCri1 As String, strCri2 As String Dim bHide As Boolean Dim xlCalc As XlCalculation
View Replies!
View Related
Hide Blank Rows Based On Values In Other Rows
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG). The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
View Replies!
View Related
Hide Rows Based On A Criteria
What am I doing wrong here? I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.
View Replies!
View Related
Hide Rows Based On Date
I need to write a macro that will hide several rows if a calculated date's month is not within the month of the report. Example: If month of date in cell B20 is not the same as the month of date in cell A5 then hide rows 20 thru 30 if it is the same month, display rows 20 thru 30. I need to do this comparison twice. If month of date in cell B40 is not the same as the month of date in cell A5 then hide rows 40 thru 50 if it is the same month, display rows 40 thru 50. The dates in B20 and B40 are the result of a calculations (Date in cell A5 plus some number of days) if that makes any difference. I don't think it should.
View Replies!
View Related
Hide Rows Based On Color
I have the following code to hide rows based upon whether or not the cell in column A is black or not. Sub hide_black() Dim Rng As Range Dim MyCell As Range Set Rng = Range("A2:A36635") For Each MyCell In Rng If MyCell.Interior.ColorIndex = 1 Then MyCell.EntireRow.Hidden = True End If Next MyCell End Sub I was thinking of taking this one step further and having the rows hide automatically once the cell is turned black allowing the user to bypass having to click on a macro button.
View Replies!
View Related
Hide Rows Based On Value In 1 Column
I wrote some simple code to work through a number of rows and hide the rows which contain a value of "XXX". For some reason, when the code hits the line denoted with? it hides the first row of "XXX"'s but then starts all over again at very first line of code outside the loop. Sheet1. Range("start_1").Select 'cell c53 Do Until ActiveCell.Value = "EOF" 'cell c266 If ActiveCell.Value = "XXX" Then Selection.EntireRow.Hidden = True '???? ActiveCell.Offset(1, 0).Select Else ActiveCell.Offset(1, 0).Select End If Loop
View Replies!
View Related
Hide Rows Based On Combobox Selection
I know there are a lot of hide row threads... i've searched for ages... but (unfortunately) i can't a solution that addresses my problem and I'm stuck. Please help! I have a data between rows 10 and 1310 and I would like to use a combo box option to select and view certain rows of data only. I don't want to use auto filter because i have a separate macro using an auto filter on the same data - they won't play together. So in short - combo box will contain values from column A. When value is selected from combo box hide all non matching rows based on column A values. So i have added a combo box to sheet1 and populated it with the following workbook code... Sub Workbook_Open() With Sheets("Sheet1").ComboBox1 .AddItem "Select Product Type" .AddItem "Apples" .AddItem "Mangoes" .AddItem "Oranges" .AddItem "Clothes pegs"........
View Replies!
View Related
Macro To Hide Rows Based On Column Value
I have read several related threads and tried to adapt their answers to my application with no success. I am trying to write a macro that will hide rows based on that row's value in a certain column. Specifically, column AB contains sums and if a sum equals 0 I want to hide that row. The sums start at AB5 and go to AB118 but there are 4 gaps in the column at rows 25, 47, 70, and 94.
View Replies!
View Related
|
TRACKER
