I am working on a sales sheet for my business. I have a worksheet that has the names of everyone in my store that has sold anything in column A. I want to create a list that has just my full time sales people and will delete everyone else.
View 9 Replies
In cells A4 to A54, their names are displayed.
In cells I4 to I54, their total sales are displayed.
Starting in cell B59 and C59 I'd like to display their name and sales respectively.
Also in column I I'd have a second value which would be a total of each department. Would it be possible to leave this data out of the formula? No big deal if I have to put that value in the next column.
I'm working through a filter macro to delete unecessary rows of data from my dataset.
- I have a Dynamic Range for my dataset called "CanadaData"
- I'm trying to delete rows from the 5th column of my dataset for cells containing "DIRECTSHIP"
The macro filters the range fine, but when if comes to deleting the row, the macro stops.
Sub CanadaWarehouseFilter()
x = Range("E" & Rows.Count).End(xlUp).Row
If Application.WorksheetFunction.CountIf(Range("E22:E" & x), "DIRECTSHIP") > 0 Then
With Range("CanadaData")
[Code] ......
In column G of my excel 2003 worksheet named: Data, I have a list of people's first names. These names were extracted from an external database.
Some of the first names actually contain peoples second names or partner's names.
Example:
John & Jill
James and Sue
Kerrie-Anne
Joanna Lyn
I need a macro to please delete people's second name.
So in the example above I need to keep all names that are joined by: & or and or - (such as John & Jill, James and Sue, Kerrie-Anne).
Also, in the example above I need to delete all names that are joined by a <space> (such as Joanna Lyn).
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI have the following codes to delete all blank rows in column A
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
MsgBox lastrow
With Sheet1
For t = 1 To lastrow
If Cells(t, 1) = "" Then
Rows(t).Delete
End If
Next t
End With
End Sub
Although it is working , it is not deleting all the blank rows at once, I have to keep pressing on the macro button running the macro several times, until all blank rows are completely deleted.
I have a long spreadsheet with multiple dependent lists. If a change is made to the spreadsheet such as deleting a row, the validation is removed. I have to re-do the validation each time I make a change. Is there a way to "lock" the validation so that whatever changes are made to the spreadsheet, the validation stays?
View 2 Replies View RelatedI have made an excel document that I use as a order form. I have size columns; qty columns; style number columns etc. After I enter in qtys to a sheet I than sort the sheet by the qty column which than brings all of my ordered styles to the top of the order form. I than delete everything that is below the last column that has qtys in it.
I have added pictures on the side of the worksheet which fit into the grids of each row that corridinates with the appropriate style number. I can sort the worksheet and all of the pictures stay in the appropriate columns; so far so good.
The problem that I run into is now when I go to delete all of the other columns that the customer didnt order none of the pictures delete. They jsut pile on top of each other and I cant figure out how to embed each picture to the appropriate row so that when I delete the row the picture deletes as well. At this point I have to go through and delete picture by picture and there are way to many pictures to do this.
How do I imbed the pictures into the row so that when i delete the row the picture deletes as well
I'm not the sharpest tool in the shed when it comes to excel programming but I manage. However, I can't seem to get rid of this run-time error 13. Could anyone take a quick peek at the document and spot the problem?
My intention is this, when typing 'Yes' in the AB column on a certain row, that row is cut from that worksheet and moved to the 'Archive' worksheet, also, simultaneously, I want to send a mail to a specific address.
Here is when I run into the error, I suspect when the row is removed with the 'Yes', those two macros clash.
macro - show rows ONLY if two columns = each other AND......
Hello Excel Swammis!
I am in need of your assistance again.
I have an Excel report set up as follows:
Col D - Order #'s
Col G - part #'s
Col H - warehouse code for each part # ("N", "M", etc...)
Col K - Qty of part # ordered
Col L - Qty reserved for that part # on the order
List of orders starts on row 9.
If a particular line on the order is filled, then Col K = Col L for that row.
When the whole order is filled, then Col K = Col L for all rows on that order.
What I need is a macro that identifies FULL ORDERS and hides all other rows. So, if Col K = Col L for all rows adjacent to same order #'s in Col D (AND if Col H has value of "N" or "M" for warehouse code), it will be visible. Any orders with even ONE unfilled line will be hidden.
I am also looking for the reverse of this for a seperate macro. IE: Any orders with even ONE line not completely filled, I want all rows for those orders to be visible and all else hidden (again, only if Col H = "N" or "M").
Below is an example of an Excel sheet I'm working with: [URL]
Basically, I'm trying to delete the duplicate rows by matching ID, Date and Type. If ID, Date and Type are the same, then, I want to only keep the record with the earliest Time in case of Type = In and the latest Time in case of Type = Out.
So, for example, in the case of ID = 1, there are 3 records for In, I only want to keep the one where Time is: 8:01 as this is the earliest. The other 2 records should be deleted.
Similarly, in the case of ID 3, I want to keep the record where Time = 18:05 as this is the later time out of the 2.
Can this be achieved by Conditional Formatting or Macro or VBA?
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...)
I also have numerous set time periods that i'm interested in.. (about a dozen or so)
for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00
What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed.
So if my timestamps were
Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2
and if my timestamps were
Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps.
and if my timestamps were:
Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
I have a Macro which deletes a sheet called "Pre selection" with:
Sheets("Pre selection").Select
ActiveWindow.SelectedSheets.Delete
It asks me every time if I am sure and I have to click to continue. Is there any way to remove this prompt or set it to continue without my intervention?
Is there any way to find out how many people are scheduled between certain time ranges.
For example : I have 5 people scheduled with various start and finish times ie.:
1. 10:00 to 15:00
2. 11:00 to 16:00
3. 08:00 to 14:00
4. 13:00 to 15:00
5. 12:00 to 16:00
Is there any way to analise this schedule in format:
08:00 till 9:00 1 scheduled
09:00 till 10:00 1 scheduled
10:00 till 11:00 2 scheduled
11:00 till 12:00 3 scheduled
12:00 till 13:00 4 scheduled
13:00 till 14:00 5 scheduled
14:00 till 15:00 4 scheduled
15:00 till 16:00 2 scheduled
16:00 till 17:00 0 scheduled
I got a code to delete all rows in the sheet which contain the word "DETAILS" but I now want to delete all the rows that do not contain the word "DETAILS"
My code if needed is:
Sub Find_details()
Dim rng As Range
Dim what As String
what = "DETAILS"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub
I want to delete every second row, because I have data with 0.25m interval and I want 0.5m interval. Why I try the following it doesn't work?
View 2 Replies View RelatedI'm looking for a macro that will delete a row with a certain value (0.00) and the row below it.
View 9 Replies View RelatedI am looking to find and delete email addresses in this case *@aol.com. I have only one column which is full of email addresses, I just want to delete all of the @aol.com ones in this case. This is the macro I am trying to use, but it does not work.
For Each cell In Range(Range("a2"), Range("a65536").End(xlUp))
If cell = "*@aol" Then
Range(cell, Cells(1, Rows.Count)).EntireRow.Delete
Exit For
End If
Next cell
I want to write a macro that would delete the rows if the sum equal to zero and the description is identical. For example:
Column a: Column b:
100 Apple
200. Apple
300. Grape
-100. Apple
-200. Apple
400. Cherry
-400. Cherry
The answer should be 300 grape.
I am looking for code to put in a macro that will go through a tab of data and delete the rows that have merged cells in them. The number of rows that there will be will never be a constant because the people using it will be pasted in information from another source, deleting the lines that have merged cells then run a macro with stuff that needs to be done to the info, but I would like to save them the step of delete the rows that have cells.I think it is Office 2013 I am using. I am not at that machine right now so I can't check.
View 4 Replies View RelatedI am able create a macro using the find function to find the next blank row, but I would like to have it select a range of rows down that I can then delete. Each time I run the macro the next blank row may be different thant the last, so it can't be a set number of row numbers each time. I could also possibly use the print area function if it would be easier.
Here is what I have so far, what is in red is where I need it to vary from next active blank row down to R2001C14, and then delete all the active blank rows ...
I cant get my macro to find a set of data, skip that row and the next row. Any rows that dont contain my data I want to delete that row and the next row.
View 2 Replies View RelatedI have a excel file with more than 10 sheets and every sheet has a title on its first three rows.
I want to delete first three rows in all sheets except parent sheet .
Is there a way for a macro to be not active when trying to insert a row or a way to have the macro understand that it's just a row shift? I'm trying to have a time stamp that anyone changes the value in a column. The following code generates an error 1004: application or object defined error when I insert or delete a row.
View 14 Replies View RelatedMy excel contains first 5 rows of heading information and the rest of the rows contain the data. I want to select a specific row from the data and run a macro that would delete all other data containing rows.
This is what I have now:
Code:
Sub DelRows()
Rows(6 & ":" & ActiveCell.Row - 1).Delete
Rows(7 & ":" & 65000).Delete
End Sub
Everything is fine if I select any data row except the first one (R6). In that case, the macro doesn't work as it should, deleting a row from the heading ones.
Create a smart macro which will remove all references to the #REF! which is left when rows are deleted. It would have to remove all trace of it from any equation it may be in (i.e. if it was in an averaging equation, it would need to remove the preceeding comma as well:
=average(a1,a2,!#REF!,a4 .........)
Is there a formula I can use, where I can delete specific rows/columns each time? I have to download a large set of data each week and each time I have to spend hours deleting the same rows and columns. For example, if I have to delete Rows 1,2,3,4,5 and Columns J,I,H,G every time, how do I automate this?
View 10 Replies View RelatedI have a workbook of which has different times in columns D and E. What im trying to do is where the time difference between the columns is LESS than 5 mins I need that row deleting and cells shifting up.
I.E lets say D14 is 05.24 and E14 is 05.50. I need that line keeping but if E14 was lets say 05.27 then I need that line deleting and cells shifting up.
I need to be able to do this right down the workbook until the last entry.
I have data from Columns A to D. I want to do the
following:
1. Look for duplicates in all column A
2. For each duplicate found in column A, check if all values in column B are also duplicate.
3. If the condition in (2) is satisfied, compare column D for all the rows; select the row with the minimum value in column D, and delete the other rows.
For example:
A B C D
Row1: Abby 04/01/2014 7:00PM 0.98437
Row2: Sam 04/01/2014 9:00PM 0.35627
Row3: Abby 04/04/2014 7:00PM 0.68932[code]....
