I have created a macro which hides columns I:O on a worksheet. Within this area I have added a combo box - but when I hide columns I:O (via the Macro) the combo box remains visible. Is there a way of ensuring the combo box is hidden along with the selected columns?
View 2 RepliesUsing two Combo Boxes, I am trying to create an drop down outline form input sheet.
What it does is if you select the first selection in the combo box 1, then it hides certain rows, and deletes certain cells. If you select the second option in the combo box 1, it shows another combo box (2) which has another list of categories.
When I select the option 2 on the combo box 1, it unhides a bunch of rows, and activates combo box 2. However, when I try to select categories on combo box 2, each time it tries to hide/unhide rows I get this message "Unable to set the hidden property of the range class." I have no idea whats going on.
Here is my ....
I have a workbook comprising several worksheets and on each of those worksheets I want to hide the rows where there is a zero in each of three specific columns on any row. If there is an entry that is more than zero in any one or more of those columns then the row should not be hidden.
In the attached example I would want to hide rows 4, 8, 12, 18 and 19 as there was a zero in each of the columns C, E and F. I would not want to hide the other rows as they have a figure that does not equal zero in at least one of the columns C, E or F in the row.
a macro for making it work on just one sheet - I can write the code to make it work for each of the worksheets in the book.
I need to filter out rows, based on a specific value in column A, (documents on file for the clients), then check column C (last name) and D (client first name), which can have the same client listed multiple times, based on how many different documents are on file ......
View 12 Replies View RelatedI have a worksheet with a large amount of data, but a lot of cells are zero (because the content of the cells is calculated). I would want to be able to build a macro which can hide all the columns whose total is zero, and also all the rows whose total is zero. The amount of columns and rows will be variable, because i expect to keep adding information continuously. The row with the totals to evaluate is 4, and the column with the totals to evaluate is H. (I cannot make a filter for this one, because there's already one some rows upwards).
View 3 Replies View RelatedIs there a simple way to hide rows and columns? A lady in my office just asked me how to make only Columns A to J visible and only show 100 rows. She doesn't want the rest of the spreadsheet to show. I know how to manually hide columns and rows but it takes a while to drag down and select 64000 rows so I thoguht I would ask the question:
Does Excel have a feature that will automatically hide all unused rows and columns or is there a simple VBA code to do this?
The macro code that will populate and input box and ask you which range of columns and range of rows you wish to hide, hide the columns and advise you via a message box that it has been completed
Is it possible to hide just a portion of a column? I want to hide just E85:E98, for example. Not all of column E.
View 9 Replies View RelatedI got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?
View 3 Replies View RelatedI have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.
On the 'Input' sheet I have a button called Format Graphs which controls the following macro:
a code that will search each cell across 4 columns and hide the row only if all cells are blank. The macro should search columns "b", "c", "e", and "f" to display all rows where at least one of the cells has a value.
Ex.
Col.B Col.C Col.D Col.E Col.F
1. 123 xxxxx 150
2. 56 xxxxx 50
3. (blank) (blank) xxxxx (blank) (blank)
In this ex. row 3 would be hidden.
I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:
Sub Check_Shifts()
'Insure all shift entries are completed
If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then
Range("G6").Value = Range("F6").Value
Range("I6").Value = Range("F6").Value
Range("K6").Value = Range("F6").Value
ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then
Range("I6").Value = Range("G6").Value
Range("K6").Value = Range("G6").Value
ElseIf Range("K6").Value < "1" Then
Range("K6").Value = Range("I6").Value
End If
If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................
Hide Multiple rows. I have text within two columns A, D. How can I Hide Blank rows which spans two column Named Ranges, "Range1" (A1:A15) "Range2" (D1:D15). As an example:
Beginning with this:
...A......D
1.Text
2........Text
3.
4.
5.
6.Text
7.Text
8.
9.
10.......Text
11.Text
12.
13.......Text
14.
15.......Text
To this , after hiding blanks:
...A......D
1.Text
2........Text
6.Text
7.Text
10.......Text
11.Text
13.......Text
15.......Text
I am working on a time management sheet for my company. I need to be able to click a button and have the file search 3 columns for a persons initials and then hide all the rows where the initials are not in at least one of the three columns. I seem to have no problem getting it to work for one column at a time, but as soon as I try to search more than one it all falls apart.
I will also need to create an unhide all button to reset the sheet after the macro has been run.
I have attached a sample of the sheet below. Each project needs to have 3 rows to show the schedule and budget broken down by each team member.
Project Number
Address
Service
PM
SS
[Code]....
way to automatically hide a row or column based on a specified cell.
Or even something general like: Hide all rows with cells evaluating to #n/a.
I've been mucking around and wondered if it possible to hide a row that has a combo box in it, that when I hide the row the combo box will be hidden to as well and if the row gets unhidden it should also unhide the combo box?
I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.
My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
I 'd like to open a xls where a combo appears only when i select a specific cell.
I created a combo in excel 97 (combobox1) and also i wrote some code for its behavior but it doesnt work.
Private Sub workbook_load()
ComboBox1.AddItem ("YES")
ComboBox1.AddItem ("NO")
End Sub
Private Sub ComboBox1_Change()
If ActiveCell.Activate = E4 Then
ComboBox1.Activate
End If
End Sub
I trying to do a sheet with one combo box (dropdown list) where I want the user to be able to choose "add rows" to make more boxes appear. My idea to solve this was to put all the boxes in the sheet, and then create a macro that either hides or shows the rows with the extra boxes. Now I have a problem that hiding the rows just does that, and only that. The rows disappear, but the combo boxes stay visible (but ends up on top of eachother).
View 2 Replies View RelatedI would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies View RelatedI am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
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.
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
I have inserted a combo box on Sheet 1 with two options in the dropdown ('A' and 'B'). I would like to write a macro to assign to the combo box so that if the user selects 'A' from the dropdown, rows 1-3 are hidden on Sheet 2, and if 'B' is selected, rows 4-6 are hidden (with rows 1-3 unhidden). I have tried unsuccessfully to write any script that even comes close!
View 6 Replies View RelatedI have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
I am attempting to design 2 combo boxs by which you can select from a number of equipment types and a criticality:
Equipment Types Criticality
Vac Pump 1A
Filter Dryer 2A
Valve 3A
I want to attempt to write a macro so that when the equipment type and criticality has been selected, a corresponding number of rows will disappear.
For example:
If Vac Pump and 1A are selected then all the rows from 70 downwards will be hidden, and if Filter Dryer and 2A are selected, then the rows from 6 to 38 and from 47 onwards will be hidden.
I want to make a combo box in excel that, if i select it the sheet inside the combo box will appear.
Example:
Inside of combox are: Sheet1
Sheet2
Sheet3
If i click combo box and i choose sheet3 the sheet3 will appear.
How can i do this? theirs a macro code to use?
The first combo box is on a userform so that a subject can be selected
View 3 Replies View RelatedI 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.