Hide / Unhide Rows With No Data
Sep 21, 2012
I think this is fairly straight forward but my limited VBA skills have me going round in circles. I would like to have a button that hides and unhides rows where Column L has blank values but only from row 80 to 200 only. I would also like the button text to change from "Hide rows" to "Unhide Rows".
From Row 80 to 100 hide rows where there is no value in column LButton should be clickable to hide rows and then clickable to unhidebutton text changes.
View 2 Replies
ADVERTISEMENT
Apr 3, 2014
I highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.
View 2 Replies
View Related
Apr 3, 2014
I have a button that I want to hide/unhide a section of rows.
This is the code a have so far:
[Code] .....
So I've named a a section of rows "rngHideRows". I've tried to use the command .Range("rngHideRows").EntireRow.Hidden = True (and viariants of this) but nothing I try works.
View 7 Replies
View Related
Jun 18, 2014
code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "B12"[code].....
I need to change it so that it hides a range of rows, say (6:26) instead of adding a line of code for each of the rows from 6 to 26 that I want to hide
Also how can I go about hiding the same rows on 5 more sheets, can I add more sheetnames after "Sheet2"?
View 3 Replies
View Related
Jun 19, 2007
Need a macro which will hide rows having 0 values. On running once it will hide those rows having 0 values and on pressing second time it will unhide those hidden rows and so on.
View 14 Replies
View Related
Mar 7, 2009
I need to hide / unhide some rows in a sheet with the help of a activex checkbox... I want remove, let's say row 20:24 and row 34 with one checkbox.
View 6 Replies
View Related
Apr 2, 2009
I have a code that will hide all rows that have a "-" in Colum A, leaving me with the useful data.
However, this data is pulled from a front sheet that has data added to it.
What I would like my code to do is to relook at all the hidden cells, and if their value has changed from "-" to what ever the data may be then to unhide that row, displaying the data.
The code I'm using currently is: ....
View 10 Replies
View Related
Jan 11, 2010
I have a list box on a worksheet that contains the following names: field, vessel, pipe, structural. I would like to be able to unhide certain rows based
on the name shown in the list box. For example, if I choose field it would
unhide rows 3 thru 12, if i choose vessel it would unhide rows 13 thru 20, so on so forth. If nothing is choosen in the list box then rows stays hidden.
View 9 Replies
View Related
Jan 20, 2010
i'm newbie here. i need to hide & unhide rows using checkbox in excel but i stuck. i write these simple code and it doesn't work at all. my bad..
View 2 Replies
View Related
Oct 26, 2011
I have a workbook with multiple tabs. Each tab has about 600 rows. In each tab, I need to hide/unhide rows based on the value of a particular column in that row. For example,
Row1: 10,7.89,John,TRUE
Row2: 16,9.08,Ram,FALSE
Row3: 98,8.09,Joseph,FALSE
Row4: 76,1.23,Harry,TRUE
Using the 4th column (that has either TRUE or FALSE), I need to hide entire row. I am using a loop on the range and hiding each row. But it is taking about 4 minutes for each tab to loop through the 600 rows and hide/unhide the required rows. Is there a faster way to achieve this? Following is my code.
Sub Toggle_Rows()
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Range("D1:D600")
If UCase(Cell.Value) = "TRUE" Then
Cell.EntireRow.Hidden = Not Cell.EntireRow.Hidden
End If
Next
Application.ScreenUpdating = True
End Sub
View 1 Replies
View Related
Nov 20, 2013
I am struggling to come up with a vba code that allows me to search column B, Rows 21:89 for blanks then hide/unhide the associated row. I would like it to be one macro so that I don't have to have two buttons on the sheet to hide/unhide.
View 5 Replies
View Related
Jun 13, 2007
I have a macro that works fine with just numbers as a test run when I use it in the desired workbook/worksheets (where the values are returned via Indedx/match functions) there appears to be a small problem. (Below is the relevant post.)
[url]
Background is that I load raw data into a monthly account sheet which has the appropriate cost centre codes for allocating expenses.
I then use a vlookup in combination with indirect to place the summary data by costcentre code into annual worksheet.
I then have a summary sheet for printing purposes so I can print out a single month expenses by code to staple to the appropiate monthly costcentre statement. This is done by using Index & match functions looking at the annual summary sheet
What seems to be happening is that the code is sometimes recognizing formulas as a value & therefore showing zero values when I want only values 0 (as I sometimes have minus values).
Also sometimes when I go to unhide the all the rows it also is recognising some formulas as having value.
I am using Xl 2003.
View 9 Replies
View Related
Dec 15, 2008
I'm using this VBA code which is hiding cells in rows 59:111. Some of these cells have formulas and some don't.
Private Sub Hide_Unhide()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 59 To 111
Rows(i).Hidden = (Cells(i, "b").Value = 0) + (Cells(i, "b").Value = "")
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Here is the problem I'm running into: I want this to run in the background so the user won't have to click anything to hide/unhide the cells whenever the options in the drop down list are changed and the data expands/collapses.
View 9 Replies
View Related
Sep 14, 2009
I am using several toggle buttons to hide/show rows of data related to the specific toggle buttons. I also want to hide and show the toggle buttons as well. When I try to do that the data underneath the toggle buttons is hidden but the toggle buttons don't hide with the cells.
A solution for the problem that I am having would be to have the ability to click on a cell as if it were a toggle button and hide/show rows of data when clicked.
OR
If there is a way to hide/show buttons as well would work too.
View 9 Replies
View Related
Nov 11, 2009
I've had luck with hiding rows with a checkbox, but I cannot get them to unhide when I uncheck the box.
Here's my scenario - (using Excel 2000) In an estimating spreadsheet I have a print range of A1:N74. What I'd like to do is add a checkbox or button so I can hide and unhide rows based on a null or zero in column range b5:b62. This is for printing purposes, but I can't get a 'beforeprint' event to work either. All I need is box checked and cells hide, box unchecked and cells show (unhide).
Sub CheckBox1_Click()
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("B5:B62")
For Each MyCell In Rng
If MyCell.Value = "" Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub
View 9 Replies
View Related
Feb 26, 2010
I have a workbook that has 500 rows. In order to be able to print the spreadsheet, I have added a Macro in that hides any rows that have a "0" in the A column. I then put an if/then formula (ex. if(isblank(A5),0) so that if the cell was blank and 0 would be there and therefore the row would be hidden.
The problem now is that I want a new row to become unhidden everytime the row above has data in it. So, if row 5 gets data put into it, row 6 would become unhidden. The problem I'm running into is that the data in column A is peoples names, and therefore each row will have a different name (i.e. different data).
1. Is there a macro to do this?
2. (This may be a really stupid question, but...) Can you run 2 macros in the same sheet?
View 9 Replies
View Related
Sep 21, 2013
I am trying to hide unhide excel rows based on the answer in "A1". Row 2 to 10 shall be unhide if answer is "Yes" and it shall be hidden if answer is no.....
[Code] ......
View 1 Replies
View Related
Jun 17, 2009
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 14 Replies
View Related
Jun 12, 2007
I have a command button that hides rows based on zero value in Col B then a 2nd btn to unhide those rows. The challenge is that as I want this code for several workbooks that will have data of varying row lengths & not all the zero values will be in the exact same place for each workbook or each month. Having done a little research on the Board (& googled) I have not found an answer to my challenge. So I am sure someone will point me in the right direction -
Private Sub CommandButton1_Click()
BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = True
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Range("A2").Activate................................
View 2 Replies
View Related
Jul 3, 2007
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 9 Replies
View Related
Aug 18, 2009
I have an workbook with many sheets. each sheet contains loads of checkboxes, dropdowns, option buttons, groupboxes ... and they are created using a macro when the workbook is opened. all this works fine. Now I've grouped different rows based on their level of importance. Here's the glitch. whenever I select a group level, the rows get hidden, but not the shapes & objects present on them. Instead, they just jump to the row above or below and overlap the other shapes.
View 9 Replies
View Related
Feb 26, 2010
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 9 Replies
View Related
Sep 15, 2004
I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size. My best effort so far looks something like this:
Sub UnhideRows()
Dim i As Integer
Dim myRow As Integer
myRow = Range("A1") + 2
Application. ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction. Sum(Selection.Rows(i)) > myRow Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub
the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with. My attempt did not work.
View 5 Replies
View Related
May 15, 2007
I'm trying to create a macro that allows users to hide or unhide selected rows, either via toggle or userform. The macro I have looks like this, but for some reason it doesn't hide the rows:
Sub Hide_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
Set UserRange = Application.InputBox _
(Prompt:="Select Range to Hide:", _
Title:="Hide Range", _
Default:=DefaultRange, _
Type:=8)
Rows.Select
Selection.EntireRow.Hidden = True
End Sub
what's wrong with my code and how I can further develop it to let the user decide whether to hide or unhide the selected rows?
View 3 Replies
View Related
Aug 17, 2007
Attached is a portion of the file I will be working with, just to illustrate what I would like to do.
I have a macro that copies and pastes data - it uses these Tasks as templates. The macro copies these rows for each task that is required under the headings (Task #1 - Task#?). What I would like is to hide the rows under Station Maintenance Task #0 and it's 6 rows that are associated with it, as well as Task #0 under CMS and the 12 rows associated with it. In order for the macro I already have work properly is that these would be unhidden for the macro to run and then re-hidden afterward.
I searched on the forum for this topic, and found hide/unhide where you would put in the beginning row and ending row - however since there will be numberous rows added in, this type of defining rows would not work.
View 4 Replies
View Related
Feb 1, 2013
I have a worksheet that allows you to choose 1-20... depending on the selection it should unhide specific rows but I keep getting errors...
Code that will allow you to unhide other worksheets in the same book????
View 1 Replies
View Related
Jun 17, 2014
I have the following code which works perfectly. It needs to hide rows 3:60 based on the value in K2.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K2")) Is Nothing Then
If Not IsNumeric(Range("K2").Value) Or Range("K2").Value = "" Then
Range("A3:A60").EntireRow.Hidden = True
Else
Range("A3:A60").EntireRow.Hidden = False
End If
End If
End Sub
The problem I am experiencing is that I want to use a formula in K2. K2 must get its value from a cell on a different sheet.
If I manually delete the value in K2 the rows hide and if I manually enter a 1 in K2 the rows unhide, however I want to enter a formula in K2 that will result in either a 1 or blank cell.
View 3 Replies
View Related
Jun 27, 2014
In the attached abbreviated example, what should the second line in each of the three macros read to direct the hiding/unhiding of rows in the adjacent sheet? Right now I'm trying a If/Then configuration but it's not working. I was able to figure this out using grouped check boxes but a list box should be graphically "cleaner" and hopefully simpler in coding. The original table list is hidden in column "A", if that is important. I could use a combo box but the menu list is short and I think the list box approach will be simpler.... If there is a way to combine the three macros into one.
View 2 Replies
View Related
Jan 28, 2013
How can i hide and unhide column and rows using checkbox in the excel.
detail price qty amount
chair 1000 1 1000
some time i just want to see only the amount or some time i want to see the qty, how can i hide and unhide row or Column
View 1 Replies
View Related
Jul 12, 2013
I have a calculated cell (D13) which can either be (All), Grade or Rate.
If D13 = (All), I would like Rows 19:52 to be hidden
If D13 = Grade, Only Rows 19:20 to be hidden
If D13 = Rate, Only Rows 22:52 to be hidden
View 2 Replies
View Related
