Hide Rows Not Working
Aug 27, 2008
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Not Intersect(Target, Range("f6")) Is Nothing Or Target.Cells.Count > 1 Then
'Application.ScreenUpdating = False
'For Each c In Range("A1152:A1179")
'If c.Value Like "Hide" Then
'c.EntireRow.Hidden = True
'Else
'c.EntireRow.Hidden = False
'End If
'Next c
'Application.EnableEvents = True
'Application.ScreenUpdating = True
'ActiveSheet.Protect
'End If
'End Sub
The cell values in A1152:A1179 are derived from a simples If formula. I have tried to change the "Hide" to a zero value but this didn't make any difference
View 9 Replies
ADVERTISEMENT
Apr 17, 2014
I'm having a problem with my macro .However, if i use the following script:
[Code]......
If i am correct all the rows with a value less than 1 should be hidden right? However, some rows are hidden when they shouldn't be.
Im working on a quotation with different products in it. And the rows with a 0 in it should be hidden and that's pretty much working. But it also hides some of the subtotals(which i dont want) and some stay unhidden.
View 6 Replies
View Related
May 26, 2014
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
View 14 Replies
View Related
Jun 9, 2013
Sub Button294_Click()
If Sheet1.Range("A34:A94") = "HIDE" Then
For Each cell In Range("A27:A94")
If UCase(cell.Value) = "HIDE" Then
cell.EntireRow.Hidden = True
End If
End Sub
View 4 Replies
View Related
Aug 4, 2014
Within a worksheet "Page_2" I would like VBA to perform following calculation: IF(COUNTIF(R:R;1);1;2)
Basically, look into column R of worksheet "Page_2" and look if there is in the column at least one number with the value 1, if there isn't show me value 2.
Depending on the value coming out of this function, VBA needs to hide worksheet "Page_3" if the value is 2 and unhide worksheet "Page_3" if the value is 1.
Here is the code which I'm using and that is not working.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.WorksheetFunction.CountIf(Range("R1:R5000"), 1) = 1 Then
Worksheets("Page_3").Visible = True
[Code]...
View 2 Replies
View Related
Nov 3, 2006
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 2 Replies
View Related
Jan 16, 2009
I have a set of filtered data and am trying to copy data from the top visible row to the remaining rows. I can get Rows.count to do this when rows are not filtered but it doesnt work right in this scenario or at least I'm not setting it up right.
View 9 Replies
View Related
Nov 23, 2008
I'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using the following
Sub move_formula_and_formats_from_I_to_L()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell
Application.ScreenUpdating = True
End Sub
The code stops working after 159000 rows and highlights (in yellow) the line:
.Cut Destination:=.Offset(1, 3)
View 9 Replies
View Related
Jul 23, 2009
Me.Rows.Autofit with a Protected Sheet not working.
Basically I want to use vb code which is
View 2 Replies
View Related
May 3, 2012
Filtering Date Column "A" 1st qtr 2012
Column "C" has numbers where we figure the average of the entire column
Formula: =AVERAGE(C36:C300)
When I filter or not the Date column "A", the Average is still the same.
How can I force it to only average out the rows displayed by the filter
View 4 Replies
View Related
Jun 26, 2014
I have a macro that I am trying to add a feature to at the end of the code that hides blank rows. I have tried numerous methods based upon other forums, but my code does not seem to work and does not hide blank rows. In red is the section of code that I am having trouble with.
Sub Update()
Dim c As Object
Dim rngA As Range
Dim cc As Object
Dim AA As Range
'Check every cell in the range for matching criteria.
[Code] ...........
View 3 Replies
View Related
Jul 17, 2014
I have a worksheet that has a quantity column "A" and if there is no value in it I would like to hide all rows without values so only rows with quantities remain. Based on the code I have supplied below you will notice I have ranges of cells that I want to work with. The code I have works well with the exception it takes a long time maybe 30-45 seconds to perform the operation. I was wondering if there is a faster way to perform the function. I have saved it as a macro which I tied to a button.
[Code] .....
View 9 Replies
View Related
Oct 6, 2008
I have sheet 1 and 2 in a work book. Sheet 1 is a list of data consisting of names and address, and quantity, type, price, etc of items shipped to them. Sheet 2 takes the data from sheet one and computes certain formulas. I have allotted up to 100 rows of data to automatically compute, however I rarely need that much. Basically it goes like this. When I open the spreadsheet sheet 1 is not actually blank, instead in cell A1 I have the phrase "Customer Name", in cell B1 I have the phrase "Account Number" and so one. I do this so my coworkers know which info must go in which cells. As long as the info is put in the correct spot than cell A1 in sheet 2 with do X and B1 will do Y and so on. so if I put in 10 customers with accompanying data then the first 10 rows of sheet 2 will compute formulas and the other 90 rows will simple state "No Data" in the cells. What I would like to do is create a macro or assign a command button to HIDE all the rows that say "No Data" in column A. So if I have 30 customers entered then when I go to Sheet 2 all I have to do is hit a button and rows 31-100 become HIDDEN, not deleted.
View 3 Replies
View Related
Feb 22, 2013
I preferrably want to hide all rows from row 58 and down, but then unhide some rows. The rows that I want to unhide is from row 1000. How many rows that I want to unhide, depends on the content of that list. If the list is empty, then I want to unhide rows 1000-1003.
What I have written (but doesn't work), is:
Code:
Range("B58:AG1048576").Select
Selection.EntireRow.Hidden = True
If Range("C1002") = Empty Then
Erstatningsgrunnlag = Range("B1000:AG1003")
Else: Erstatningsgrunnlag = Range("C1001:AG") & Range("C1001").End(xlDown).Row + 1
End If
Erstatningsgrunnlag.EntireRow.Hidden = False
View 3 Replies
View Related
Oct 29, 2013
Rows 9-79 - if value in column D is zero, then hide row.
How do I do this?
Also - can this be triggered by just selecting the worksheet, or will I have to use a button?
View 4 Replies
View Related
Aug 21, 2007
Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually.
View 9 Replies
View Related
Jan 8, 2008
I have a worksheet "ULIP21.xls", where in cell C10, the value can be either "Yes" or "No". If the value is "No", I want the rows 31 to rows 82 hidden in the sheet "INPUT" of the worksheet.
View 9 Replies
View Related
Feb 13, 2009
I want to make a macro that will recognize a value in a cell and then hide
rows that I don't need.
For example: cell is Y2
If I put into the cell the value of "abs" i whant to hide rows from below that do not contains(the rows are Y5 :Y25) "abs"
View 9 Replies
View Related
Jun 17, 2009
i've run a quick search on the forum and have picked up a few ideas but wonder if someone can help write some code?
I need rows 10:14 to hide in sheet2 if cell b13 in sheet 1 is blank?
View 9 Replies
View Related
Sep 13, 2009
I'm creating a roster which effectively needs 5 discrete pieces of information per cell - one main piece and 4 notes.
The creator of the current worksheet used comments for this function. They can't be printed, searched etc, you can't work with them and because the info can't be added with data validation it's inconsistent. What a nightmare.
I've rejected the 3d option across sheets because of the complexity and limitations of 3d calculations.
My solution is to have the main info in row 1 and the other 4 items in rows 2-5 and then repeat downwards so there are 5 rows per day.
My question is this - is there an easy way for users to hide and unhide the 4 extra rows? I need to improve readability but the extra info needs to be quickly and easily accessed by some users. Everyone else just needs to see what they are doing each day. This roster is a year long downwards so manual hiding and unhiding rows isn't practical.
e.g.:
1camerasounddirectetc>>>2hide details monwho is actually workingmarytomdick3who should have beenmungomidge4why the changesicknessholiday5type of coverovertimefreelance6details of changedouble time invoice no 10247hide details tuewho is actually workingharrymungodick8who should have beenmarymidge9why the changeswapped shiftsholiday10type of coverstafffreelance11details of change40080 invoice no 102412etc13 /
and when hidden, this:
1camerasounddirectetc>>>2show detailsmonwho is actually workingmarytomdick3show detailstuewho is actually workingharrymungodicketc /
View 9 Replies
View Related
Jan 21, 2010
I run a report everyday that I have to sort many times and remove unwanted rows before its down to the data that I need. What I want to try and accomplish is to say that IF column B or C contains WORD1 or WORD2 to hide that ROW that its in. Also if Column D is greater than 400 to hide that row. Better yet delete it! Below would be an example of raw data, then below it what I would only want to show:
Advisory
NIGO
IGO
127.6712/07/2009 10:1612/07/2009 12:23Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
PENDING
125.0712/07/2009 10:2312/07/2009 12:28Retail
IGO
IGO
422.6512/07/2009 10:3112/07/2009 12:34Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
Worksheet would just show:
Retail
IGO
IGO
117.412/07/2009 10:2712/07/2009 12:24Advisory
IGO
IGO
82.5712/07/2009 11:2512/07/2009 12:47
View 9 Replies
View Related
Jan 29, 2010
I have a workbook with a number of worksheets that come in twice a week. On each sheet the layout is identical as shown below with the data for each group of cities starting at row 4 for 4 rows and then continuing for a few hundred rows without a break. The city in A4 and every 4th row is merged with the 3 rows below
Unfortunately the cities do tend to change so a recorded macro would not be reliable.
A............................. B
Birmingham.....line title
merged.......... line title
merged.......... line title
merged.......... line title
Glasgow........ line title
merged................ line title
merged................ line title
merged............... line title
What I would like is to list the cities that I am interested in in the code and for it then to hide all of the other blocks of 4 rows for cities not listed.
So using the above example if I Birmingham was not listed in the code then its 4 lines would be hidden and only Glasgow would be visiable
View 9 Replies
View Related
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
Dec 22, 2008
How do I select all the Columns and Rows that are not being used so that I can hide them. I like the blue background it gives when they're all hidden. I am using Excel 2007 and it's not too bad selecting all the columns but there are just way too many rows.
View 3 Replies
View Related
May 22, 2009
Is there a way in 2007 to automatically hide rows that don't contain data? (I don't want to delete them, just hide them).
View 3 Replies
View Related
Aug 22, 2009
for statement to hide rows VB. In VB, how would the following look:
View 2 Replies
View Related
Sep 6, 2007
I've a worksheet that contains a whole list of items in stock.
For example
Item Quantity
hot water bottle
maggi
fab
cooking oils
breakfast oats
The above is an example of what might be seen in the spreadsheet. What i'd like to be able to do is before printing it out, i want items with 0 quantity to be shown only. So i decided to hide the rows that have items with no quantity. To do this, all i could think of is to have a button that may contain codes to hide the rows. The problem with that is the button will appear there in the printout. Is there any way of making the rows hidden before printing without using a button to trigger the code?
View 14 Replies
View Related
Oct 22, 2008
I want to autohide any row that does not have a number in the Current # of Bins column.
View 2 Replies
View Related
Dec 23, 2008
How would I write a macro to hide a range of rows If a cell value is zero, then do the same for five additional ranges of rows?
In my words:
If AT214=0, hide rows 214 to 244
and
If AT245=0, hide rows 245 to 278
and
If AT279=0, hide rows 279 to 311
and
If AT312=0, hide rows 312 to 344
and
If AT345=0, hide rows 345 to 377
and
If AT378=0, hide rows 378 to 410
View 4 Replies
View Related
Aug 14, 2009
With the help of Venkat1926 I established a macro that successfully hides a number of rows that are unrquired (based on dates):
View 14 Replies
View Related
