Hide Range Of Cells
Apr 29, 2008
I am creating a form in excel and need to hide a selection of cells, not an entire column, or an entire row, but the cell range K43:K48. I want these cells to be hidden at all times unless any one of H44:H48 has the value "Other" inputted.
If Other is selected I want range K43:K48 to become unhidden. Is there any way to do this?
I have tried to use Conditional Formatting to do this, but only being able to use 3 conditions makes it harder to do when I have a range of 5 cells that could display "Other:"
View 3 Replies
ADVERTISEMENT
Aug 17, 2012
I have a file with four columns of data that represent quarterly figures. I would like to hide the entire row if all four quarters are blank. The cells I need to check are P, Q, R & S and the data begins with row 21. I know how to get the last row in the range.
View 5 Replies
View Related
Dec 19, 2009
I'm newer in VBA. I will appreciate for help creating vba procedure. I would like to hide/show range of cells by that condition:
View 11 Replies
View Related
Jan 17, 2010
I am looping thur an array to Format A range of cells. After the format is complete I need to validate that all of the cell in a range are empty if so hide the whole Column.I was Try to do it like this. I am not getting an error but nothing seems to happen either. Also The Boder of the cell only appers on the Last cell it should be on the cells....
View 3 Replies
View Related
Mar 18, 2014
I am very new to VBA. I need to have a code that will hide/unhide cells based on a date range.
Cell A1 contains the formula for todays date, based on that answer, I want to hide/unhide columns based on a weekly date range.
Ex.:
Today is 03/18/2014 I want excel to say If A1(Today) is greater than 03/14/2014 and less than or equal to 03/21/2014 then unhide column E and hide the rest.
Then let's say Today is 03/22/2014. I want excel to say If A1(Today) is greater than 03/21/2014 and less than or equal to 03/28/2014 then unhide column F and hide the rest.
columns in questions are D-AC
View 3 Replies
View Related
Sep 11, 2013
I have a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia
What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want
1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C.
2. Basis the range I want sheets to show or hide.
View 1 Replies
View Related
Jan 24, 2004
1. I can create basic macros .. but how do I write a macro to hide a range (of 10 rows) named "dmargin". That for my button no. 1.
2. How do I write a macro to unhide the named range "dmargin" for my button #2.
I tried using VB codes to achieve both conditions -- it works but it either continuously protects or unprotects my ws -- so I think it is better to use a low level codes (macro buttons).
View 5 Replies
View Related
Dec 21, 2007
If Range A1:a10 On Sheet 1 = 0, Hide Sheet 1
Anybody Know The Code To Do This In Vb?
View 9 Replies
View Related
Feb 3, 2009
If range B10:O21 has a value that is listed in column A28:A168 then hide the row where it is.
Example if I have in B10 the number: 2151
I want the row in column A that has 2151 to hide.
How can I write code to do this?
Would this be with worksheet change?
View 9 Replies
View Related
Nov 21, 2007
Suppose I have ten Names in Excel, and I dont like any one see them, How can I hide them (Names not cells)?
View 2 Replies
View Related
Oct 23, 2007
Can't seem to find a solution searching through various forums searches. I would like to hide all blank rows within a Named Range : Range1 (A1:E8).............
View 4 Replies
View Related
Oct 26, 2007
I have this code (compliments of VBA Noob) which hides all blank rows within a range ("Range1") P16:V650. It works great in a new worksheet with little amount data, however within my heavy worksheet, it takes over a minute to compile.
View 2 Replies
View Related
Feb 8, 2012
I am familiar with the custom formatting option to hide cell contents when you don't want them displayed in either a chart or when printing: Custom > Type > ;;
I was trying to create a macro that will search a specified range and whenever there is either a zero or nothing displayed in the cell (even though a formula exists), I want to custom format the cells to not be displayed.
My range is Range("B5:I16")
I tried to create something like
Code:
Dim dataRange as Range
Set dataRange = Range("B5:I16")
For each Cell in DataRange
if Cell ="" then Selection.NumberFormat = ";;"
I'm struggling to figure out how to tell Excel to step through each cell in this range, evalaute whether there is a blank and if so, apply the NumberFormat ";;" to it, otherwise let it be.
View 3 Replies
View Related
Nov 26, 2013
With my searches i manage to make this working code:
Code:
Sub Hide_Rows()
Application.ScreenUpdating = False
Dim Rng, os1, os2, os3, os4, os5, os6, os7, os8, os9, os10, os11 As Range
Set os1 = Range("B11:E63")
Set os2 = Range("B64:E116")
Set os3 = Range("B117:E169")
Set os4 = Range("B170:E222")
[Code]...
In any range (os1 to os11) all empty rows are hidden... ok! But, every range have 2 first rows as headers that i want to hide (in case of no data below) before print!
View 4 Replies
View Related
Jan 17, 2007
I'm trying to hide all rows from row 3 to a variable row. I've struggled to simply select the rows that I want to hide. I have managed to select a range (containing the rows that I want to hide). I now need to select the rows contained in the range currently selected and then hide them. My code is as follows:
Sheets("Program").Activate
Activesheet.Range("B3").Select
Sheets("Program").Range("B3", ActiveCell.Offset(LastProgramRow - 10, 0)).Select
Selection.Row.EntireRow.Hidden = True
The last line of code fails. I have tried modifying it to variations of:
Sheets("Program").Row (3), ActiveCell.Offset(LastProgramRow - 10).Select
but this also fails. How can I change this code so that only the row is selected and not a column?
View 2 Replies
View Related
Apr 29, 2008
I am trying to hide columns within a named range, where the range corresponds with the values section of a pivot table.
My code hides an unexpected set of columns. It seems to be affecting a different range, depending on the number of columns in the pivot table, which is not part of the named range, as if there was some kind of offset.
Sub hideAll()
With ActiveSheet.Range("wholeyear")
.Range(.Columns(1), .Columns(12)).Hidden = True
End With
End Sub
View 5 Replies
View Related
Jul 22, 2009
I have the following Macro that hides rows that contain old dates (in column B) in a range:
View 2 Replies
View Related
Nov 13, 2011
I want to filter and then hide the filtered output in a range of data. But after I hiding, when I remove the filter, everything is there again. I don't know how to hide it even filter is removed.
View 7 Replies
View Related
Aug 20, 2008
I have Cell C63 in Worksheet 1 which is set up to say either "YES" or "NO" depending on whether a value on a different worksheet exceeds a value in an adjacent cell or not (also on that worksheet). The formula I am using is simply:
=IF('4. NORMALITY ASSESSMENT'!AC55>'4. NORMALITY ASSESSMENT'!AA55,"YES", "NO")
Essentially I don't want anything displayed if there are no data in the range C4:C54 on Worksheet 1. I thought I need a nested IF function but I couldn't get this to work.
View 5 Replies
View Related
Aug 17, 2006
I have a graph in Excel containing the 12 months in column 'A' and data associated with each month in column 'B'. I want to hide rows containing months which have yet to arrive (eg: if we are in August, I want to hide the rows containing September:December). However, I want to change the range of rows that I hide each month (so that when we get to September, I only hide rows containing October:December). My attempts at writing a macro to perform this automatically have failed? This is the script I have written:
fyi - RowStart is the row number associated with January, RowEnd is the row number associated with December and RowNext updates each month and is the row number associated with the next calendar month.
Option Explicit
Dim RowStart As Integer
Dim RowEnd As Integer
Dim RowNext As Integer
Sub Hiderows()
RowStart = sheets("month").Range ("A1")
RowEnd= sheets("month").Range ("A2")
RowNext = sheets("month").Range ("A3")
Sheets("month").Select
Rows("RowStart:RowEnd").Select
Selection.EntireRow.Hidden=False
Rows("RowNext:RowEnd").Select
Selection.EntireRow.Hidden=True
End Sub
View 2 Replies
View Related
Oct 27, 2006
Having a problem with a survey I'm creating in Excel.
I have a question with a Yes/No response cell. I want to show/hide several rows below it. The problem is that these rows also contain 6 option buttons, which I also need to hide/show depending on the response cell.
Is there a handy way to hide all of these shapes/controls in the defined range, and then "unhide" them if the cell value changes back to "Yes"?
View 7 Replies
View Related
Aug 10, 2007
I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.
View 2 Replies
View Related
Oct 25, 2007
I have 3 columns L, N and P
I would like to return the MIN value(column R) between the columns
except where the MIN = zero then just leave the result as blank.
View 9 Replies
View Related
May 15, 2008
How do you select certain ranges in the same column to filter? The ranges I need are "c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202" all filtering for blanks. I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.
View 7 Replies
View Related
Aug 14, 2008
I have a spreadsheet with a number of sheets two of which contain tables with many columns with a date heading, I would like a means for the user to select a range of dates and for the spreadsheet to automatically hide any columns that don't fall within this range.
View 3 Replies
View Related
Mar 18, 2009
There are empty cells in a column.
Where the cells are empty, they are reading #VALUE! because I have copied down a formula in all other cells above and below etc.
Is there any quick way of making the #VALUE! disappear or by hiding?
(Other than the copied formulas in the column, there are also conditional formatting so when the numbers drop below zero value, it turns the numbers red).
It's a long sheet and I want to avoid deleting each #VALUE! by hand!
View 12 Replies
View Related
Nov 24, 2007
I have column b with 30 rows of names, I need to search on at least 3 characters to retrieve all names matching the 3 consecutive characters.
All other rows are to hide, and the remainder should all be displayed now starting in the row 3 position.
My problem is that if a match is found in lets say rows 8, 99, 500, and 2300 the rows inbeweeen are not hidden.
Sub SEARCH_TEXT_STRING_AND_Hiding_Routine()
Dim foundCell As Range
With ThisWorkbook.Sheets("Sheet1")
Set foundCell = .Range("b:b").Find(what:=Left(.Range("B2").Value, 3), After:=.Range("B2"), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
View 9 Replies
View Related
May 14, 2007
I have a work sheet with 4 pages. I enter a value on page 4 & copy it to page 1. The formula
=complete!(h4)
entered on sheet 1 will enter a $0.00 untill I have a value entered on sheet 4 (which is named complete)cell H4. I want to hide all those zero's, in a search I found this code,
Option Explicit
Sub HideCol()
Dim cl As Range, rTest As Range
Set rTest = Range("a1", Range("a1").End(xlToRight))
For Each cl In rTest
If Not cl.Value > 0 Then
cl.EntireColumn.Hidden = True
End If
Next cl
End Sub
Is this the best way to hide all those zeros? & when I get a value in cell h4 will the it then show?
View 9 Replies
View Related
Mar 17, 2014
I have a row data corresponding to the measuring of load cell per min and I need to average the values per hour. So I have a column B for the date (from 1-01-2013 to 31-01-2013, column C for the time (0:00:00 to 23:59:00), and D de values per min I want to average. I have to do the same for the rest of the month of 2013 (February, ...., December).
I would like to know if there is a way to create a formula to calculate the average of the first 60 values (to get the average of the first hour of 1-01-2013), and then copy it to get the average of the following 60 (average of the second hour in 1-01-2013) and so on.
If there is no way to do it, I would like to know if I can do it using functions like average, match, index, offset, what would be the best match of those functions.
I also tried it by doing the analysis in another tab and using the function "averageifs" with two criterias: one for the date (example 01-01-2013) and another one for the hour (example 0:00:00), but it didnt work, it show error: #value. I inserted an extra column in the data tab with just the hour (example 0:00:00) in front of the corresponding column with (example 0:01:00, 00:02:00, etc)
Equation I used for this:
=AVERAGEIFS('Data (min)'!D$6:D$43206,'Data (min)'!$A$6:$A$44646,A6,'Data (min)'!$B$6:$B$44646,B6)
=AVERAGEIFS(TAB AND COLUMN WHERE THE RAW DATA IS,RANGE OF CRITERIA 1,CRITERIA 1,RANGE CRITERIA 2,CRITERIA 2)
View 2 Replies
View Related
Jun 10, 2014
I have the following macro to autohide some rows with no data, however its remove rows starting from column A.
[Code] .....
How do i fix this to only hide the rows in the range (H4:V50)?
View 5 Replies
View Related
