I have a workbook with 7 different Sheets to pull data from. On Sheet 3 named "PMP Mileage Linear". I have 4 drop down boxes. In one drop down labeled "Drop Down 4" there are 21 different choices to choose from. Based on the choice I would like to hide or unhide Rows. Example: if a user chooses "3yr/36,000 Miles". I would like to hide rows 16:51. If a user chooses "3yr/60,000 Miles". I would like to hide rows 29:51 (hence un-hiding 16:28). If a user chooses "5yr/100,000 Miles" I would like only rows 41:51 hidden and so on until a user hits the max of 125,000 Miles (5,6 or 7Yr) whereas no rows are hidden. I would have attached the spreadsheet but I don't have permission from the people who own the data.
I have a shared worksheet that has many rows of data... but only certain rows are relevant to each specific users. I would like each user to be able to select their name from a drop-down list in order to have all rows that are not important to them be hidden. I can put the name of the user in column 'A' in each row they need to see, but how could I make all rows that do not contain their name in column 'A' hide? In other words, if 'Bill' is selected from a drop-down list, only rows that contain 'Bill' in column 'A' would be seen.
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes.
The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
I have a workbook with 4 sheets relating to areas and a summary sheet and introduction.
On the introduction sheet I have a dropdown which lists all the available products my company make.
What I want is to be able to select the product from the dropdown and then click a button which will then copy and paste the headers and any rows which relate to the selected product from each of the area sheets and paste it all onto the summary sheet.
I am currently working on a workbook for work. It is a basic input output sheet. I have data from work that i copy and paste into sheet 1 and I have it arranged into sheet 2.
I am using windows 7 with Excel 2010, though needs to be compatible with 2007 using XP.
What I am trying to implement. I would have a drop down box in cell AD-4 with the current list
Select Week 1 Week 2 Week 3 Week 4
What I want to implement is a code that if I select "week 1" from drop down then it will hide the entire row if data in column B = week 2, week 3, week 4, week 6
also
If week 2 is selected then hide entire row if column b = week 3 , week 4, week 5
so basically hide the weeks that are after the current selected week.
In addition to this I would also like to hide the entire row, regardless of drop down selection if data from J, K and O all = 0 or (blank).
(the first row is the current Row labels, I have a lot of current hidden columns).
A B J K O
Supervisor Name Agent Name Needs Complete Completed w/o Turn in Needs Turn In
I have a large spreadsheet that has some duplicates in Row Q. However, in column L there is a date. I want to be able to find the duplicates in column Q then delete the duplicate with the earliest date.
Is it possible to update certain rows only based on the choice selected from a dropdown list
I shall explain this scenario
I have attached a small excel sheet called TESTTHEM which i'm testing and learning excel simultaneously. On the attached sheet
If i select a value in B4 as BusinessLogic, it should then lookup for relevent rows in MISC worksheet and copy C5:L5 from MISC worksheet and replace the columns G4:P4
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then If Range("A2") = "MOT/Service" Then ActiveSheet.Columns("A:P").EntireColumn.Hidden = False ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
i have a dropdown list called SN (for serial numbers). items are abc001-abc100. i would like to remove an item once ive selected it. i.e. if i have chosen abc001 on the first row, when i go to the next row and open the dropdown list, abc001 should not be there anymore. this is for me to avoid duplicate listing of the items.
I would like to be able to make the same thing and select items for my drop down list, that are not blak and follow a specific condition. But I don't know how to include the condition in the array formula.
The condition I need is follow:
I want to have a dropdown list with Items that are not blank, and First N > Last N.
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB: Public Sub DelRows2() Dim Cel As Range, searchStr, FirstCell As String Dim searchRange As Range, DeleteRange As Range
i have a sheet with three dashboards built in and want to dispay on certain data depending on what is selected in a drop down menu (E8)
All - All rows are unhidden UKIRSA - Rows 133 to 192 Glasgow - Rows 72 to 92 Sykes - Rows 12 to 70 Header - Rows 1 to 11 Footer - Rows 193 onwards
I want the header and footer to remain the same but the middle body of the dashboard to change depending on the selected option ie only the rows for the option are displayed, eg if Sykes selected then Glasgow and UKIRSA ranges are hidden
This is the code i am trying but it is not working:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(False, False) = "E8" Then Select Case UCase(Target.Value) Case "Sykes": Rows("12:70").Hidden = False Case "Sykes": Rows("72:192").Hidden = True
Case "Glasgow": Rows("12:70").Hidden = True Case "Glasgow": Rows("72:132").Hidden = False
Case "UKIRSA": Rows("134:192").Hidden = False Case "UKIRSA": Rows("12:132").Hidden = True
Case "All": Rows("12:192").Hidden = False End Select End If End Sub
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.
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
Problem: The code works fine if i manually change the $D$10. If I make changes to the Drop Down Box the cell link updates, but the rows are not hidden.
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.
I have a worksheet that collects information from another using complex formulas.
In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.
PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.
In column K is the sum of columns D:J.
What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.
Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.
Sub Remove_E_H_Ts() Application. ScreenUpdating = False Dim x& For x = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1 With Cells(x, 2) If Left(.Value, 1) = "E" Then ' i need to add If Left(.Value, 1) = "E" or "H" or "T" Then .EntireRow.ClearContents End If: End With: Next x ' after clear contents how can i remove blank rows ? Application.ScreenUpdating = True End Sub i am trying to make this line of code work below....
If Left(.Value, 1) = "E" Or "H" Or "T" Then
then also after clear contents how can i remove blank rows ?
I have a macro that performs Index&Match formulae on another workbook. This leaves me with some #N/A results. I then require the macro to delete all rows that contain #N/A. I have tried to identify these via
If Range("E" & myLoop).value = "#N/A"
and
If Application.WorksheetFunction.Isnumber(Range("E" & myLoop).Value) = False
but each one gives me a Run Time Error 13 (Type Mismatch) because, even though the results are now values and not formulae, it sees '#N/A' as an error (Error 2042).
I have a large file and I am doing a subtotal here. I want to do further processing of the "subtotal" data (basically, I want to sort the list on the subtotalled sum values) and I would like to know if there is any method to delete all rows except the "subtotal" rows from the sheet other than the method described here, in another post in 2003.
Copy subtotals only - visible cells too complicated [SOLVED]
This works fine but I have the word Total in some of the columns I am sub-totalling.
Also, some of the entries in that column are very long strings to start with and when the subtotal is made by Excel, the details are truncated (probably to 255 characdters) and the word Total is not there.
james - smith - leeds- 01535 - 26/02/1983 - bd21 6ls steven - smith - bradford - 213789 - 28/01/1982 - bd33 5th james - smith - leeds - 01535 - 26/02/1983 - bd21 6ls steven - king - manchester - 213789 - 28/01/1982 andrew - wright - bingley - 36473 - 12/01/1981 - bd23 689
what im trying to do is to check all of the rows for duplicates in such a way that (if any row has the matching colums 1,2,5 and 6 then all but 1 of the rows will be deleted.
i have a few thousand entries and am just trying to get rid of all the duplicate rows. The problem is that the duplicate rows are not IDENTICAL for instance soime of them may have spelling mistakes hence i cannot just do a straight forward if row = row then delete.
I have been asked to edit a worksheet for a client, they want me to create a dependent drop list using existing data from three columns.
I have created a dynamic named range and used and index to create the drop down list, which seems to work correctly. I run into a problem with I try to add the list to more than one column, as it obviously pulls in values from the Helper sheet I have used.
When the user tries to select values in row one after making a selection in row two, the values available for selection are incorrect.
Is there any way to have a three column dependent drop list that can be used in more than one row?
I have uploaded the file here: ownCloud
The information on the tab named 'Cabling Ducts' is used in an index on the 'Helper' sheet to remove duplicate values. The drop down list columns appear in 'CS 1' under the headings 'Cable Type', 'Size (mm)' and 'Cores'. Helper sheet is self explanatory.
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 need to get the output as shown below which is for Batch number P2 I need to include Item 1 (568) in one row and delete the other P2 row. similarly I need to do for P3. The end result will be as shown below.