I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible. If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I have an Excel workbook with multiple sheets. In one sheet, there are many columns that automatically get hidden based on cells values (=1) in another sheet as I type. I use this
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$7" And Target.Value = 1 Then Sheets("Stakes").Range("E:E").EntireColumn.Hidden = True Else If Target.Address = "$C$7" And Target.Value <> 1 Then Sheets("Stakes").Range("E:E").EntireColumn.Hidden = False End If End If End Sub
Recently I had to change the second sheet that instead of manually entered values, formula results appear in cells. And the above code doesn't work anymore, columns are always stay unhidden. How can I achieve what I want? I need columns get hidden if formula results =1.
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
I have one worksheet that contains a large table. I'm using VLOOKUP to spread each row of the table to separate worksheets.
When VLOOKUP refers to an empty cell, is there a way to set that row to be hidden?
Also, if VLOOKUP returns data to a cell, is there a way for Excel to automatically set the row height to display all of the linked data in that cell? There is only one column of data.
I've written a macro to unprotect a sheet, unhide columns and reprotect the sheet. It works fine with 2 out of 3 worksheets. I cant find any reason why it wont work in the one sheet....
I am trying to hide/unhide several columns by hitting a button. It's a pretty simple macro to do it once, but how do I get a button to hide on the first click, then unhide on the second?
I have a report that has percentage of row totals that just displays as 100% for the grand total column. I want to hide that percentage of row column. I've attached the excel file below to see the pivot tables.
I have a macro that looks at a list of students and the school they attend, and then creates an individual worksheet for each school containing only their students. I want to hide Column B on each of those worksheets. This seems like it should be easy, but it is not working.
When I leave it as is pasted below, Column B does not get hidden.
When I change it to Sheets(school.Value).Columns("B").Select I get an error message stating "Select method of Range class failed."
Sub ExtractSchools() Dim wsTransfer As Worksheet 'worksheet with transferred data from registrations wrkbk Dim wsList As Worksheet 'worksheet with list of students Dim wsNew As Worksheet 'worksheet being added for a school Dim wSheet As Worksheet 'name to loop through all worksheets
Dim rng As Range Dim school As Range Dim rowNum As Integer Set wsTransfer = Sheets("Transfer") Set wsList = Sheets("Student List") wsTransfer.Select Range("Database_Transfer").AdvancedFilter xlFilterCopy, Range("Criteria"), _ wsList.Range("Database_Unique").........................................
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.
How would you right code to automatically sort (using the same sort parameters all the time) when the value of a column changes?
Here's the scenario: Columns B through N are populated.
The sort parameters are (in order) column F ascending, column D ascending and then column B ascending. The sort in column F is based on a custom list (but I doubt that matters here).
As far as the number of rows to select, it varies but there is always a blank row at the bottom of the selection.
What I am looking for is code that will sort the selection (currently its B4 through N37) in the above order automatically when the value in any of the selected rows changes in column F.
I would like to have a macro that will hide a column based on the value in the row. I have multiple sheets and if row 4 (or more specific, B4:AL4) contains a zero, then I'd like the whole column to be hidden on that sheet. I'd like to be able to run the macro and it evaluate every worksheet.
I have to create a spreasheet monthly that has perhaps 60 transactions. The data is in columns A - G, with columns E and F having the costs figures. I need to sum the value in column E and also in Column F every few rows. The problem is that sometimes there are two rows per transaction and other times it could be 30 rows per transaction. Where I will be lucky is that there are two rows between transactions, and the sum goes in the first empty row.
Basically what I would need it to do, from a macro point of view is to go down column E, and every time it finds the first empty row, insert an auto sum. It would then need to ignore the next row, because it too, is empty.
Just Column E - I need an autosum in the "empty row 1's)
I have an Excel database of department contracts with a Master worksheet. I have created a worksheet tab for each of the departments. Column G has a dropdown list for each department. When I enter a new contract onto the Master I want it to auto copy to the coordinating worksheet based on the selected department.
If possible I would also like it to enter the newest contract would enter into the coordinating Contract Party entered into column D. (ie... If Hospital A is entered in column D on master, the new entry on the worksheet being copied to will be entered under the last Hospital A, if Hospital B is entered on Master it copies under Hospital B). If this isn't possible then I am not too worried about that part.
I have attached a blank version of the file. Current Master 5-15-14.xlsx
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.
I am having a hard time making a macro. Using Excel 2003 i would like to Auto-hide a row when a particular cell has one of three characters entered into it. "W", "L" or "D". If those characters are not in the cell it would be blank.
is there a way to auto hide a row depending on the data in two cells. Basically if two specific cells are empty or return a zero or specific number i want the row to auto hide.
I have an Excel schedule with subtotals and between 1 and 50 rows under each subtotals. The subtotals contain totals of the rows below them. Is it possible to link code to an object (say a 'down arrow' next to each subtotal) and when it is hit, it unhides the rows beneath the subtotals? At the same time the 'down arrow' changes to an 'up arrow' and when that it hit it re-hides the rows.
There is also the possibility that the user will unhide rows and move down the spreadsheet unhiding/rehiding other areas before coming back and wanting to re-hide the first area he revealed. When the spreadsheet is closed, all the arrows should revert to 'down arrows' and all the non-subtotals' rows should be hidden.
In sheet 1, I have a list of data in A2:D21. In sheet 2, I have formulas in the same range that "paste link" the data. If, there is no data entered in any entire row of the same range in sheet 1, then I want the entire row to hide automatically in sheet 2. In fact, unless there is something entered to start with, I do not want any of the rows to be visible. Is this possible and how?
I got an excel, with a "validation list" in a certain cell.
Users can select value1, value2 or value3. based on this value, some columns will have to unhide (standard= hidden) (only for value2 this is the case).
I used the worksheet_change event to determin the value, but so far I'm only getting it to hide...
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If C24 = "value2" Then Columns("H:O").Hidden = False Else Columns("H:O").Hidden = True End If End Sub