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?
I 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 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)
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.
Sub Test() 'for Macro to Delete Duplicate Rows and Retain Unique Value Dim LR As Long
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
I 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.
I 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)
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.
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:
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
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
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
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 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.