i have a speadsheet with 2 columns
1 good work
2 good
3 excellent
4 needs improvment
i need a macro that goes through each row and does a search in column B for a partial match of a word. if that word isnt present in the row, the row is deleted.
if i typed in a criteria of "good"
rows 3 and 4 are deleted.
so my speadsheet will then look like this
1 good work
2 good
I run a match formula which returns the row numbers of items i need to delete - at the moment, when a match is found, it will say "Match found, delete row 4" for example. Since multiple matches, potentially into the hundreds could be found - is there a way that i could run a script to delete the rows automatically when a match is found? So instead of putting the above "match found, please delete row 4" into an adjacent cell, could we just delete row 4? & then move onto the next row where a match is found?
View 9 Replies View RelatedI need some assistance with the code below. I have a userform that has a listbox with 2 columns of information. When there is a match between the value in the listbox and the value in the cell for a given row, that row is deleted. I thought this was very straightforward, but i'm not getting this to work right...
Private Sub cmdDelete_Click()
Dim Val2 As String
Dim Val3 As String
Dim doit As Long
Dim roww As Long
Dim LastRow As Long
Val2 = ListBox1.List(ListBox1.ListIndex, 0)
Val3 = ListBox1.List(ListBox1.ListIndex, 1)
doit = MsgBox("Confirm that you want to delete the entry", vbOKCancel)
If doit = vbOK Then
I've got a file set up like this:
1001 Value 1
Value 2
Value 3
1018 Value 1
Value 2
1023 Value 1
Value 2
Value 3
Value 4
1029 Value 1
Value 2
Etc.
Now I want to delete the sections where the 1000-something value ends on 3/4/7/8 and keep the sections where the 1000-something value ends on 1/2/5/6/9/0 So the result would look like this: ....
I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)
View 5 Replies View RelatedI have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".
The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.
So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.
I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.
This is the code I wrote:
Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String
Application.ScreenUpdating = False
[Code] ........
So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.
I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows
I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.
Code:
Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long
[Code].....
I have attached a sample of my data. What I am looking for is the best (most elegant solution) to my problem.
I am trying to create a macro where I have 2 sheets. Sheet 1 will contain my data, sheet 2 will contain a single column of criteria to search through sheet 1.
I would like for the macro when it finds that criteria, it will delete all rows of data that is tied to that ORDER_NUM
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2113510395CANADA EMPACT20100108IN445N3113510395CANADA EMPACT20100108IHT9534113510395CANADA EMPACT20100108ID981C5115436953CANADA EMPACT20100110IPMDMN6115436953CANADA EMPACT20100110IN246F7116292017CANADA EMPACT20100111IY1G0M8116411203CANADA EMPACT20100111IT571R9119169642CANADA EMPACT20100113I9M62C10119169642CANADA EMPACT20100113IT272C
For example, it my criteria was part N445N, I would like for it to delete all rows of data that is tied to that ORDER_NUM (113510395).
Desired Output
Schedule
EFGHI1ORDER_NUMAREADL_DATESHIP_CODEPART2115436953CANADA EMPACT20100110IPMDMN3115436953CANADA EMPACT20100110IN246F4116292017CANADA EMPACT20100111IY1G0M5116411203CANADA EMPACT20100111IT571R6119169642CANADA EMPACT20100113I9M62C7119169642CANADA EMPACT20100113IT272C
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
ColumnA in my data base contains dates. How can I delete the entire row if the date is before today's date and add a new date at the end of the range to replace the deleted row.
I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.
View 5 Replies View RelatedI am trying to write a code to copy rows from sheet 1 to sheet 3 if cell of column A "account #" on sheet 1 are matching cell of column A (account #) on Sheet 2. If multiple entries find on sheet 1, (e.g. acct # 101c shows twice) copy all matched rows to sheet 3 as well.
View 3 Replies View RelatedI need to retrieve information from multiple rows in a table based on certain criteria (date compared to today's date, existence of "no" in a column).
View 6 Replies View RelatedI have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.
I need INDEX/MATCH to return the correct values for the subset below.
Column A (Forecast Status) - Column B (Account Name)
Commit - Account01 (Row 1)
Commit - Account02 (Row 2)
Won - Account03 (Row 3)
Won - Account04 (Row 4)
Won - Account05 (Row 5)
Upside - Account06 (Row 6)
Lost - Account07 (Row 7)
Upside - Account08 (Row 8)
Won - Account09 (Row 9)
Commit - Account10 (Row 10)
In a new tab, I am creating a 'dashboard' view that will group each of the following:
Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)
Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)
Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)
Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)
My knowledge of Excel is quite basic, so i had little luck searching for this one.
We have a main data sheet in which everything is inputted, but i would like that data to be split up and reported (copied into) in 2 or 3 other sheets, depending on a single criteria.
Is this able to be done so these 'report' sheet are constantly updated, and the columns in the reports dont include irrelavant columns which are in the main data sheet.
I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.
=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))
Code reflects the ALL GRADES data below.
TERM SUBJECT NUMBER CRN CREDITS GRADE ID
1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890
Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!
345678 3 1 A A #NUM!
456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11
* Need all grades where TERM = Min of Term
I have a file which has some data I want to keep and some data I want to strip out.
The data I want to keep will always have either a date or a string “Overdue” or a string “> 1 year” in the first column, anything else I want to remove.
Example file shown below
23-Apr-0812341234123412341234
30-Apr-0812341234123412341234
19-May-0812341234123412341234
27-May-0812341234123412341234
> 1 year12341234123412341234
Overdue12341234123412341234
There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:
Sheet1 looks like this:...................
I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.
I've attached a mock report up so that maybe it would make more sense.
So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.
Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.
So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach.
Find the next instance Name & # matches, and pull that into rows 12-16, etc.
I did a search to find a question similar to mine and I found this:
Copying data matching a criteria into another spreadsheet
This is exactly the same concept I am trying to accomplish, but don't know how to...at all. I couldn't follow the thread either.
I have a schedule which is constantly being updated (simplier version attached). I want to create a schedule, which will:
1) First ask which region to display the information for (I would like to create the same pop up menu to choose from)
2) Then, it will select only that region's information (all rows & columns) and copy them into a new worksheet and lists it all
-if you go back to the Master List, you can again choose another Region and it will create another new worksheet with that region's information
3) Don't know if this is making things more complicated, but at the bottom of the attached file, there are 3 lines under "Land".
Is it possible to list these under the same spreadsheet as the region it is pertaining to but classify it separately under "Land" (ie leaving space, so it is clear this is separate
I have a need to delete multiple rows with multiple criteria; criteria contains about 400 different and unique entries that have to be deleted. The code I obtained from this forum was modified but only deletes some entries and bypasses others. Row 1 is header and column 1 contains criteria that it it meets the " Case" it is supposed to be deleted. Example of code is below the Case has been shorted, for posting but normally each case runs the maximum length allowed. Please let me know where I messing up, I am thinking the it needs to be looping until all entries are deleted but I don't know how.
Sub DeleteRecords()
Dim n As Long, lastrow As Long
Dim ty As String
Dim a As Integer
lastrow = Range("D65536").End(xlUp).Row
a = 1 'this is the column in which you will find the notation finance numbers (Col X = 24)
For n = lastrow To 2 Step -1
ty = UCase(Cells(n, a))
Select Case ty..........................
I want to delete a row in a spreadsheet by finding the row that contains a particular value. I use MATCH on spreadsheet #1 to identify the row number on spreadsheet #2 to delete. I need the macro to select the row number on spreadsheet #2 and delete it.
I don't know the syntax for the commands in VBA to write this and I'm coming to this forum for help because I'm too lazy to learn VBA. I've recorded macros to add rows, populate cells, hide sheets, etc. The following code is extracted from a macro I recorded to try to get started, and was hoping to be able to substitue a reference to sheet1 within the parentheses at
Rows("4:4").Select
but I can't make this work. The following is the code I was trying to adapt.
Sheets("Sheet2").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
End Sub
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies View RelatedI 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
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
The first code hides everything just fine based on the date in A1. When I change it to the second code to delete instead of hide it is leaving a bunch of rows that the 1st code hides. Both codes have the same search criteria.
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Hidden = True
Next cell
Code:
For Each cell In Range("B8:B5000") If cell.Value Range("A1").Value Then cell.EntireRow.Delete
Next cell
I 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 am having trouble coming up with an algorithm for deleting columns, based on a certain set of criteria. Heres the issue, all the columns have a "1" the top of column . If that column has a cell number that's greater than or equal to 0.90 or less than or equal to -0.90 then that column gets deleted, if it just has a "1" then the row doesn't get deleted. Very complicated set of criteria.
View 6 Replies View RelatedI 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 am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.
View 2 Replies View Related