Combo Box To Remove Mulitple Rows
Jan 12, 2010
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.
View 10 Replies
ADVERTISEMENT
Mar 18, 2009
I'm trying to write a macro that inserts multiple rows by giving user a MsgBox to enter the # of desired rows. Meanwhile, I need to make sure that the formulas in certain cells are filled down (not just copied from cell directly above). I put something together but it's only adding a single row. Attached is my sample workbook.
View 2 Replies
View Related
Jun 4, 2009
I added a combo box to a worksheet and closed and saved it. But I don't want it amymore. I cannot delete it. I try right clicking it but nothing. The worksheet,(workbook) isn't protected.
View 2 Replies
View Related
Apr 9, 2007
I have 4 combo boxes that allow me to make selection from pivot tables. As you know in the pivot table there's the "All Selection" which does not exist in combo boxes (I think), so I created a macro to reset all the pivot tables with the "All" option selected in all of them. However the values in the combo boxes are not changing.
View 14 Replies
View Related
Aug 8, 2013
i have a list of 5 teams in a named range ("teams") within a worksheet.
On a userform I have 5 combo boxes.
What I would like to have is an easy way to remove a used name in the list for the next combo box.
i.e. someone selects team a in combobox1, combobox2 then has a list of team b,c,d and e. I'll be locking the combo boxes and writing code so the next one will unlock if the previous box is populated.
i also need this to work if someone puts team c in first combobox this then gets removed in combobox2 then in combobox2 they input team e then both these values are removed for combobox3.
only way I can think of doing it is creating a lot of named ranges and using a lot of if then code which I'd like to avoid.
View 2 Replies
View Related
Aug 18, 2006
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 Related
Dec 8, 2006
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 Replies
View Related
Sep 19, 2009
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?
View 9 Replies
View Related
Oct 9, 2008
The first combo box is on a userform so that a subject can be selected
View 3 Replies
View Related
Dec 4, 2008
I am trying to using multiple if staements regarding one cell. What I am trying to achieve is:
if b2=0, then null, however if b2=1, then arable, however if b2=2, then manged grassland, however if b2=3, then forestry/woodland, however if b2=4, then semi-natural vegetation, however if b2=5, then urban, however if b2=6, then water
I have these 2, but they don't work - what am i doing wrong?
=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))
=IF(B2=0,IF(B2=1,IF(B2=2,IF(B2=3, IF(B2=4,IF(B2=5, IF(B2=6, Water, null), Urban),Semi-natural_Vegetation), Forestry/Woodland), Maneged_grassland), Arable), null)
View 5 Replies
View Related
Oct 21, 2008
How do you set the code so that, if a condition is met, then several cells are filled with values?
So far, my code is
HTML If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If
View 11 Replies
View Related
Sep 8, 2005
i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name occurs on.
Vlookup returns only one value. How can I get multiple values?
View 14 Replies
View Related
Jan 14, 2009
I have a worksheet that has three columns, TYPE, VALUE, PERIOD.
Within TYPE column it will always be either A or B
Within VALUE column a number common to both A & B for that "period".
Within PERIOD column a month-year.
A shown here:
TypeValuePeriod
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
B3000Jan-09
B3000Jan-09
B3000Jan-09
A3200Feb-09
A3200Feb-09
A3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09....................
View 9 Replies
View Related
Jul 30, 2008
I have a report that I paste into excel. The report contains 4 columns. Column A contains a numeric value. The numeric value relates to a geographical area and the same numeric value may show up more than once. The reason the numeric value may show up more than once is because in column B there is a list of dates showing when a percentage in column D become effective. I have attached an example of the report.
What I am looking for is a was where the user can enter a geographic number in cell J4 and a control date in cell J5 and have cell J7 display the percentage value. I need the lookup to look up the correct area and then use the control date to find the corresponding percentage. So if Area 1 (numeric value) shows up in Column A in rows 2 through 5, the control date will determine which row to pull the percentage from using the effective dates in column B.
View 3 Replies
View Related
Nov 6, 2009
I need to highlight cells for all of the products and rates I need to get pricing for. I have a macro that goes out to my data and pulls in each different rate and program (columns BM:BN). That macro also sets the first note rate in the range to the lowest of the group, so this will always be different.
I now manually highlight each cell that I need to get pricing, but would like to create a macro that looks up those codes and rates in column BM:BN and highlights the cells for me. I need it to highlight in all 7 boxes. I've attached a sample worksheet of what it looks like after I highlight the cells.
View 2 Replies
View Related
Feb 20, 2006
I have 2 drop down lists made, but I want to have one linked to the other
one.
For example the first list has types of wood in it.
Bamboo
Maple
Oak
The 2nd one has pricing in it.
1.25
2.50
3.50
If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
be done?
View 14 Replies
View Related
May 4, 2008
I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.
Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).
I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.
View 10 Replies
View Related
Apr 25, 2007
I am attempting to create a macro that does the following. I need to copy the active workbook to 25 new or existing workbooks without showing "save as" etc. These workbooks will reside in the same directory as the active wb. Problem is, I have multiple directories that I will need to do the same action.
C:ClientClientNameExample1.xls (only has sheet1)
---This will be the master which needs to be copied to 25 more times like below.
C:ClientClientNameExample2.xls
C:ClientClientNameExample3.xls
C:ClientClientNameExample4.xls
C:ClientClientNameExample5.xls
etc...
View 9 Replies
View Related
Sep 15, 2009
I am using the code below to search my entire workbook for a value entered by the user at a popup.
View 2 Replies
View Related
Dec 29, 2006
On sheet 1 there is a dropdown box that indicates a condition. For example the choices are 1-4. On Sheet 2 calculations are made based on the condition in the drop down box.
I also show the condition # on sheet 2 with a simple =sheet1!a1. What I would like is the ability to have the drop down box on both sheet 1 and sheet 2 and that if you change the value in either place then is changes in the other. Same way that you can operate a light with 2 different switches.
View 9 Replies
View Related
Dec 31, 2009
I have a workbook with 26 multiple worksheets that each represent 1 of 26 payperiods per year (bi-weekly payperiods). These worksheets consists of rows of employees names and hours worked each day. The columns consist of dates representing each day in that payperiod.
I need to be able to search all the worksheets (ie. search all 26 payperiods) to find specific 30 day intervals and then copy each employees hours worked to a master spreadsheet. I have tried variations of Index & Match formulae as well as vlookup/hlookup/ISNA without luck. I am not able to use VBA.
View 5 Replies
View Related
May 14, 2006
Im sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select
View 5 Replies
View Related
Jun 10, 2008
Each salesman has an enquiry log / hot prospect sheet that they are responsible for.
At present at the end of the month our receptionist has to consolidate this information manually onto a master sheet.
I would like to create an automatic master sheet that auto updates each time it is opened.
It has to read data from about 13 work books each with varting amounts of date. All works books are formatted identically (or they will be)
View 14 Replies
View Related
Apr 30, 2008
Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.
View 10 Replies
View Related
Apr 20, 2008
I am trying to get a VBA code to import multiple text files into an excel worksheet. The text files contain about 5,000 words each listed in a single column. I found this thread Import Multiple Text Files and the code listed there:
Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "c: est" '<- change to actual folder path
delim = vbTab '<- delimiter (assuming Tab delimited)
fn = Dir(myDir & "*.txt")
Do While fn <> ""
Redim b(1 To Rows.Count, 1 To 1)
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)..........................
View 8 Replies
View Related
Apr 4, 2014
I am using the following code to remove an entire row if a specific cell in that row contains "remove":
iLastRow = Cells(Rows.Count, "D").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Left(Cells(i, "D").Value, 4) = "remo" Then
Rows(i).Delete
End If
Next i
However, from what I can see, it starts from the bottom and look upward, removing each line as it finds it. The problem is, there could be upward of 500 lines that need to be deleted. This coding looks and removes lines one at a time and can take a while. Is there any way I can get this done faster?
During the previous steps of my macro, I sort the column (Column D) that has the "remove" in it, so all of the "remove"s are adjacent to each other in Column D of my file.
View 5 Replies
View Related
Oct 31, 2013
I'm puzzled on coming up with a way to delete a set of rows in Excel. I have two columns like below and would like to remove the rows with the later year. Is there a formula or vba I could use to run on 5000 records?
Current table:
Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12
[Code]...
Desired table: [this would exclude rows where record source = MCL-INV-SUR09
Job Code Id
Resource Source
77224L HIMCO
MCL-INV-SUR12
[Code]...
View 4 Replies
View Related
Mar 29, 2007
How to remove unique rows from excel-table and leave only dublicates? any macros?
View 9 Replies
View Related
Jun 10, 2008
I have a customer database that is sorted by phone number. How do I remove duplicate rows.
View 9 Replies
View Related
Jun 20, 2008
I have data in a 3 column by massive amount of rows (10000+) periodically there is about 6-11 rows of 'header' data which needs to be removed. this is a report run monthly so doing this over and over drain time. I need to create a query that will find all of these rows and delete the entire rows.
View 9 Replies
View Related
