Is it possible to close rows based on conditions? e.g if A1 contained Yes or No as the switch to close and a column contains open or closed in each row for the trigger. So if A1=Yes all rows with 'closed' will be hidden, if A1=No then all rows are shown
View 8 RepliesI'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.
VB:
Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range
[Code]....
Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''
I'm trying to create a condition that hides rows when certain cells are equal to zero (column D + E + F). Anything else, I would like those rows to remain visible. Ideally, I'd like for this to be applied with the use of a macro (button) and then also deactivated when not desired (possibly another button). I've seen some varieties of code for this but none have worked flawlessly yet. An example sheet I would like for this to work on is my "ADM" sheet", among others.
View 2 Replies View RelatedFirst, I realize there are plenty of hide cells threads but I have yet to find one pertaining to my situation and I apologize in advance if I this solution has already been posted.
The Problem: I am looking for macro code to a toggle button that will hide various rows that have no value between multiple sets of rows. The toggle should refresh the format of the rows as the information that was blank could later on have value.
The Setup: For each set of rows, the first row will have the label T and the last will have the label S. In between T and S there can be data. If the cells between T and S are all empty then the rows including T and S should be hidden other wise only the non blank cells between T and S should be visible.
The Reason: I have a master database worksheet, there are four copies of the master database worksheet each entitled phase1, ""2, ""3, & ""4 respectively. These phase sheets are linked to the master sheet and show the entire row's data based upon the beginning cell of each row showing either phase1, ""2, & so on.. The data is broken up into many sub databases and traditional auto filters or advanced auto filters will not be applicable as the title of the data and the empty rows in between need to be hidden if the data is empty.
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.
One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.
If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.
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
as per the attached, need to insert those grey rows subject to the following condition :
if current row date <> next row date, .and. current row latitude / longitude <> next row latitude / longitude , insert grey row with date = current row date, else insert grey row next row date
note that the coordinates in the repeated grey rows, for the "Home" location, are the same through the sheet, should be entered by the user, at the beginning of the process, since there will be a spreasheet per user.
date is in column K
latitude / longitude are in columns B / C
this will be of tremendous assistance in automating mileage claim review.
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.
I would like to delete rows that are based on these conditions: First ,Do a loop from row 2 to last available row. - Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.
- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main. I had attached an simple example with the 2 sheets. The Result are shown in the example.
I would like to delete rows that are based on these conditions:
First ,Do a loop from row 2 to last available row
- Delete rows with same column(column B) that has the same value. However I want the last available row with the same ID to remain.
- Delete Rows with any values in found in other sheet column B. When the value taken from the first sheet(ABC) is compared to the column B in second sheet(DEF), if they are equal, the row will be deleted in the second sheet and the rows in first sheet will main.
The comparsion of ID are compared between sheet ABC and sheet DEF
(the Highlighted are those rows to be deleted based on those conditions)
The final outcome are shown on col H I J for sheet ABC and col I J K for sheet DEF.
ABC
ABCDEFGHIJ1QtyProduct
QtyProduct
2100111Case 1
100222Case 33100222Case 3
50333Case 1450333Case 1
57444Case 3557444Case 3
150666Case 26200555Case 1
100111Case 17150666Case 2
100888Case 38100111Case 1
231999Case 19100888Case 3
200555Case 110231999Case 1
100112Case 411200555Case 1
100113Case 112100112Case 4
100114Case 2 13100113Case 1
100115Case 114100114Case 2
Need to sum through an unknown number of row generated by advanced filter at run-time
Formula at C16
Data starts at row 57 (from copy/paste - advance filter)
Last row unknown
Criteria to match in A16
Data to be evaluated in B57:B (row unknown)
Found this formula on microsoft.public.excel.misc
=SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
Which I modified formy start row
Im making a timeplan for projects in our company. Each Sheet is for one month.
So long I only found macros for copying rows if checkbox is true, but without the date condition.
In cell C2 I have the current date shown with today() formula
On the end of the month for example on Sheet called "May" I want that
all rows witch are unchecked that means unfinished (H6;H50 are checkboxes)
are copied to the next Sheet "Juny". This must happen only once a month (on the first day of next month). Example: On Sheet "May" this macro should be run only on 1st of Juny.
The rows should be copied without spaces between rows (if rows 6 and 8 are unfinished in Sheet "May" the next Sheet "Juny" should contain this rows in line 6 and 7).
combining 2 rows of data if certain conditions are met. I have attached an example of my worksheet for reference.
Example.xlsx
What I want to happen is if a number in column J is followed by an "x" then the values in columns AQ and AR should be combined with the row of the same number (e.g. if 236x is in column J2 then the values in AQ2 and AR2 should be combined with AQ3 and AR3 because 236 appears in column J3, the same process needs to be repeated for 237x with 237 etc.).
I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:
1Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet
Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions.
I have the following data
Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060397MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060397MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060397MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---
976533060398MAADXB-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAALIT-DFWCDHJKL3SD RAJESHFirstDFW---
976533060398MAADFW-DXBCDHJKL3SD RAJESHFirstDXBUS--
976533060398MAADFW-LITCDHJKL3SD RAJESHFirstLITUS--
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAANBO-DXBCDHJKL3SD RAJESHFirstDXB---
I need to delete rows based on following conditions, If,
Ticket number, origin, FB Data, Desc, pax name is same i need to delete all rows except the Row which has Destin filled in. In the above example I need rows only with NBO
Result should be as follows,
Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
976533060398MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
I need one more criteria to be tested the above result. IF,
origin, FB Data, Desc, pax name is same and ticket no is A1+1 i need to delete the second row
Final result should be
Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin
976533060397MAADXB-NBOCDHJKL3SD RAJESHFirstNBO--NBO
I need a macro that loops through all rows (With the exception of the first row) and looks for two conditions. If those two conditions are met, I'd like for it to delete the row.
The first condition is if Column O does not contain a certain phrase "VTX" VTX isn't the complete value of the column, it might just be contained within a few other notes in that column.
The second condition is if Col S equals "COMPLETE/FOLLOW-UP IMAGING".
So if these two conditions are met, Col O does not contain the word "VTX" and Col S equals "COMPLETE/FOLLOW-UP IMAGING", then that row should be deleted.
I am needing to format a spreadsheet using 2003 which only allows 3 conditional formats, but I have 4 conditions.
I need to highlight the row if column W has a
G - green (colorindex = 35)
R - red (colorindex = 3)
Y - yellow (colorindex = 36)
O - orange (colorindex = 44)
how I can do this?
I have a sheet caled "Data", with thousands of rows.
I want to delte any rows that have duplicate rows only if the following data is in the same row but if in column A, coloumn C and column H have the same data
A B C D E F H I
w 1 1 3 4 5 6 7
e 1 1 1 3 4 5 8
w 2 1 5 2 5 6 7
The last row would need to be deleted as dupilcates are A C and H
The data is not sorted.
I'm trying to code a clean up of some data ranges ready for priting / reporting. I need to move filtered data from the source sheet, to a new one.
The issue is, that I need to apply some logic to the rows I filter out, not something I can do with autofilter...
- Only copy rows including and BETWEEN the first and last rows where total count in col 8 is > 0
So in example1, I'd want to copy out rows P6 - 1A
BUT
- Always copy at least 5 rows when there is one value > 0 in col 8
So in example2, I'd want to copy out rows P5 - 1C, ideally...
Total
Boys
Girls
Total
[Code]....
I need to eliminate the passing jobs, leaving the failing ones so I can do coaching sessions with the guys that are missing the mark.
Conventions of the report:
Each job has a 20 digit job # and is allocated 12 rows worth of information fields unless customer has more than 1 cable outlet with a modem, then the rows increase by 4 for each additional modem
Tech name is repeated after every 12 rows even if within the same job #
On passing jobs, column I will only contain “Yes” (there are blank cells in column I, as not every parameter is checked on every job), failing jobs will contain “No” in column I
Items I need to accomplish:
· Increase column C width to 22 and column E to 25
· Format column C to number, 0 decimal places (to avoid scientific notation)
· Move tech name to beginning of each job # and eliminate repeated names
· Delete all rows plus tech name on passing jobs
· Change cell color to red in rows (columns A-I) that contain “No” in column I
I have highlighted each different job # in different color
John Smith10002934422610200000Outlet 2D - Up Stream SNR274029.4Yes10002934422610200000Outlet 2D - Up Stream Tx365546Yes10002934422610200000Outlet 2D - Down Stream .............................
I have been trying to work on this thing for like 3hours and i could not make even a single line since I dont get what loops I should make and how I should do it. Could someone suggest me how to do this.
1. check the date.Calculate two weeks backwards.
2. Check for id no which is scanned more than once in that two weeks from the current date.
3. Check for type of ppe if its different then leave it if they are same and exceeds 3 of the same type it should BOLD the badge id.
Have attached the sample file.
I am trying to create a dashboard of reports. I want to be able to click a button and the user will be taken to the tab with the appropriate report. I am not a programmer so I am trying to use Excel formulas to the fullest.
I am trying to create a 30/60/90 report. I am using a named range for all data. Is there a way to create a dynamic named range and specify for rows where the age column is between 0 and 30, 31 and 90, 90 and 120, and so on?
I have an excel template that is used for communicating in projects to teams their responsibilities and defining which stream/group has Accountability/Responsibility/Consulted/Informed (known as a RACI model) for the activity.
For any given project activity, there are rules:
One person is responsible for performing the activity (R)
Only one person can be accountable (A)
Many people can be consulted through the activity (C)
Many people can be informed of progress (I)
You must have an A and and an R for each activity
And one person can be accountable AND responsible A/R
Given that project activities can run to 100 + activities in some of our more complex programmes.... I'm looking to provide a flag in the spreadsheet for when an error has occurred and a gap in Responsibility or Accountability is created. (Less worried about testing for C's and I's)
See attached, I hope it is clear.
I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.
When "dates" or J2:J25 is less than or equal to today's date
AND
"HTeam" or W2:W25 is equal to Civil
AND
"Percent" or K2:K25 is equal to 100
I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):
Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub
As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).
I am having a problem finding or creating a macro for what i am needing to do. I have a workbook with 2 worksheets (2 months july and august) to figure out a billing process.
I need to be able to First of all search each value in Column A on sheet 2 (august) to Column A on sheet1 (July).
My sheet is set up like this where ID is a buyer and Products 1,2,3...are if they have purchased that product from us
A B C D
1 ID Product_1 Product_2 Product_3
2 100 0 0 1
3 200 1 1 0
4 300 1 0 0
5 400 0 1 0
Better Picture representation attached
Both worksheets are very similar but August may have some changes from July such as new ID's and changed prduct purchases. What I need to do is have each ID in Column A be compared to Column A in the July sheet, but it may not always be the same cell (because of new ID's added they are moved around)..so it need to search for the same ID first then compare. Once it finds the ID, I then need it to compare the rest of the row (the product info) for that ID to see if any products were added or changed to determine billing. And for any changes that are found in the comparison, I need them to be highlighted.
Now in the case that a new ID was added...it wont be in the July sheet, so the whole row would need to be highlighted to notify of a new ID and new billing needs to be added.
This way the billing person can look at the sheet, see the highlited changes and see what is new or needs to be added to a previous ID's billing. Currnelty they are looking at it manually to see if there are changes.
Each task in my spreadsheet has multiple jobs assigned to it. Frequently users will work two jobs for the same task simultaneously and log the time together.
Unfortunately when I go to try to sum up how much time was spent on each task, this causes it to appear as if the task took twice (or even twenty+) times as long as it truly did.
I want to be able to figure out the time spent on each task for each user. In order to do this, I need to get rid of rows where the task id and the user id are both duplicated in another row.
Ex: ...
Maybe this has been taken care of in another thread, but still:
ROW ID NUM OCC K OCC I
1 222222 0 0
2 222222 0 0
3
4 333333 0 0
5 333333 0 0
6 333333 0 0
7
8 444455 1 1
9 444455 1 1
I have a situation like above, but what I want to do is:
=FOR(ROW1:ROW9, IF COUNT ROWS(ID_NUM)>= 2,
(create name for set?)
AND
IF OCC_K AND OR OCC_I VALUES >= 0,
THEN DELETE ROWS(within set) >=2
Or more simply:
If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?
Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.
Is it also possible to use AND and OR in the same line? Or BOTH?
(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)
I would like to think I'm not going to need a macro, and that I can do this in a Formula.
I have a dataset that, due to the nature of how it is generated, separates certain orders into separate rows. Which orders these are is irrelevant, I just need a quick and easy way to make excel re-combine these rows in to one entry, and sum up the data in one particular column in the process of doing this.
The separate entries of a single order can be identified by the data in 3 columns matching - I have attached an example.
The constituent parts of the same order can be identified when the country, city, and code columns all have matching data as you read down. E.g., where
Country City Code
a 1 12345
a 1 12345
a 1 12345
Where this is the case, I want excel to automatically sum the count of shops for this order and then delete all but one of these rows, with the remaining row now containing this summed figure. Failing this, a new worksheet being generated/a new column giving me this data would be good.