I use this code to create sheets for courses but the student appears more than once - see attached Sample (Before sheet).
How can the code be changed so that the student only appears once on the sheet? - see attached Sample (After sheet).
Sample.xlsx‎
I was wondering if it is possible to delete rows if a value appears only 1 time in a column? To clarify since 123 only appears once in column A I want the entire row 4 deleted.
View 2 Replies View RelatedI have a spreadsheet that I need VBA Code to do the following
1) Delete all rows where the word totals appears in Column B for eg "Totals for Vehicles Stocked in 0706" "Totals for Vehicles Stocked in 0707" etc
2) Delete all rows where the value in col m is less than 45
I have written the code, but cannot get it to work-see my code below
Sub Del_TOTALS_Underaged()
FinalRow = Cells(65536, 2).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 2).Value >= "Totals" Then
Cells(i, 1).EntireRow.Delete
If Cells(i, 13).Value < "45" Then
Cells(i, 1).EntireRow.Delete
End If
End If
Next i
End Sub
I have a long spreadsheet and want to remove rows of information from it if cells contain a specific word. If the cell in column A contains the word "district" or "borough" anywhere in the string of text and irrespective of the case I need to delete the whole row.
View 4 Replies View RelatedI am trying to write a macro where it will delete rows in Sheet A if any condition is found in the row which is based of conditions in Sheet B.
Sheet A :
Column A Column B Column C Column D
1234 ABC tyu 4588
asd qwe www 4455
zxc zdaa 1234 4441
ghj llll 1111 poo0
Sheet B (where i kept my conditions in Column A: )
Column A
1234
1111
(and many more....)
the end results will be the Row 1 and row 4 will be deleted. I have tried to google but most only contains deleting rows with a specific conditions.
I want DELETE the rows that contains 0 (zero or -) in column F (SALDO BRUTO), when I click the Button (Clear). And How I can copy from this sheet to a new sheet in this workbook, but the names of new sheet automatic rename to next date or 2, and next when I click the Button "Copy to New Sheet".
View 2 Replies View RelatedI am trying to create a macro that would be able to delete an entire row of data if a value in column A is equal to a range on a different sheet.
On Sheet “ISQ Raw Data”
If any value in column A (starting in row 2) is equal to a value on Sheet “Old Response IDs” Range A:A(all of column A)
Then Delete that entire row in Sheet “ISQ Raw Data”
Is there a macro to delete an entire row if a duplicate entry appears only in a certain column.
1. Look for the column header with the name "File Number"
2. Anytime the same number under the "File Number" column appears more than once in that column, keep the row that contains first occurrence of that number buy delete the entire row anytime that number is repeated in another row in that same column.
This is regardless of what is contained in the other columns. For example..let's say these cells contained this data...
B1 - UTE00225
B2 - UTE00546
B3 - UTE65513
B4 - UTE00225
B5 - UTE00225
In this case, I would want to keep rows 1, 2, and 3. But, I would want to delete rows 4 & 5 because the number "UTE00225" has already appeared first in B1. I'm using Excel 2003.
My project looks like this:
I have a report that starts in column "A" and ends in column "J", I created several formulas in each cell, say, starting with column "K" till column "AA" to retrieve multiple data.
Not all formulas get values, there are many blanks. Longitude of the report is variable.
Now, I copy the content of K:AA and paste values only into new sheet. And that works fine.
Problem starts when I want to delete all empty rows in this new sheet.
Seems like after I paste, cells which I see as empty are in fact not empty, but non of them contains neither values nor formulas.
I tried 8 different ways of deleting emty rows and nothing works. They only delete empty rows above the content that I paste.
I have some data from A1 to A65536, there are few blank rows in between. i want to delete all the rows below the word "test" in that range. that is the word "Test" appears in A37686 that row and all the below Rows till the last row should be deleted.
View 9 Replies View RelatedI am trying to select all the data in every row below where the first "flat" entry in the worksheet is but it will only go to the bottom of the data in the column where it finds flat. Some data in other columns extends beyond this so it is not working for me. It would work for me to select everything from where it finds flat to the very last row 65536 but I can't figure out how to do that with what I have. I am then deleting all this data.
Cells.Find(What:="flat", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
range(Selection, Selection.End(xlDown)).Select
I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.
View 2 Replies View RelatedI have got a set of data in a workbook with Sheet 1 range A1 to K373 and a range of data in Sheet 2 ranging from A1 to A30. What I will like to do is to have a VBA code to loop through all the cells in Sheet 1 column C and check which are the cells which value matches those in Sheet 2. Those that matches in Sheet 1 will have the entire row deleted. I have come out with the following codes but it does not work.
[Code] ..........
I have an excel list of My Existing Customers and have recently purchased an excel list of all possible customers in my market that happens to have my existing customers listed in it as well. How can I remove my existing customers out of this purchased list so that I can import it into my Database as Prospects? I'm using Excel 2010. Deleting duplicates doesnt work for this. I want a function that looks at data in one spreadsheetA and if it finds it in the second spreadsheetB, it deletes the row out of SpreadsheetB.
View 9 Replies View Relatedfew macros/vba that delete rows based on criteria in a cell.
I'm after something a little different. I have a workbook with sheets called Raw Data1, Raw Data2,Raw Data3, Raw Data4 and Raw Data5 and Menu.
There is a column of data in each of the sheets which has a column header of 'Location', however the column where this is located will vary from sheet to sheet. For example in Raw Data1, this is column 'J' and on Raw Data2 it will be column 'M', ( I'm not at work and can't remember the specific column positions for all sheets re: 'Location').
Is it possible to have on the 'Menu' sheet, say in cell B5 a value of 'Locationa' and have rows be deleted in each of the Raw Data that do not match the value in B5?
I have a worksheet that is composed a header row, and columns of data (client call results).
Each client is identified by a unique ID number in column A, a seperate column F has the 'call number, from 1 to x'.
So a client can have a single or multiple rows of data. Each client group of rows is sequenced with call 1 as line 1, call 2 at line 2, etc. etc. as your proceed down the rows.
The entire worksheet is already sorted (with a header row) ascending by client id and call #
What I want to accomplish is this
1 - copy the existing worksheet (CallRecords) to a new worksheet (CallToday)
2 - in the new worksheet, scan down (or up) from first data row (2) to the last row {ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row} and delete duplicate rows of the Client ID retaining the row with the max call number for that client id.
3 - sort the new worksheet (CallToday) ascending by column (G) date, and column (H) time.
1) Highlight column A, and do a search on the word "Item"
2) Once it finds the word item, move 2 rows above it, and delete all rows above the word "Item"
The word Item at times can be on any row, and its very random, but its always in column, A. I tried doing it with a regular macro, but it seems to remember the row that I used, rather than two rows above the word "Item".
I have 2 spreadsheets of names (~2500 and ~1800) and a bunch of corresponding data continuing down the row. both are structured this same way:
Example:
Row1: LAST, FIRST, data1, data2, data3, etc...
is there a formula which can "check" the larger sheet for duplicate names (a row with exactly the same FIRST and LAST), and then either:
1) delete these rows from the smaller sheet
2) clear the contents of those rows
3) or at least flag them in some way so I can quickly delete them
it would be quite a task to eyeball and remove these rows one-by-one, so i'm wondering if a formula could somehow do it (I don't really know anything about visual basic)
One other piece of information which might be important:
For these rows containing duplicate first & last names between the 2 sheets, the entire row is not a duplicate entry; only the names will match (columns A & B)... The other columns down the row will have different values between sheet1 & sheet2. Not sure if this changes anything....
I have a range of data that is sorted (about 20,000 rows). In about 15,000 of these rows column D will contain a generic text string. How do I select the entire row where the generic text string appears in column D?
View 3 Replies View RelatedI have a worksheet containing codes in column A. Where the first 2 digits of the code contains "78" for eg 7801, 7825, 7892 etc then the entire row must be deleted.
View 9 Replies View RelatedI have found some excellent code that exports rows to individual sheets based on values in a column, and it works perfectly. I have found some code that deletes any rows that do not contain today's date:
VB:
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("O" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If Range("N" & i).Value < Date Then Rows(i).Delete 'N is column "Treatment Date"
Next i
Application.ScreenUpdating = True
But I'm having trouble adding this to the code I'm already using (that is working):
VB:
Sub Copy_To_Worksheets_2()
'Note: This macro use the function LastRow and SheetExists
Dim My_Range As Range
Dim FieldNum As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim ws2 As Worksheet
[Code]...
I tried adding it after the code " ' delete columns (after exporting from Current Patients)" but I received the error "Compile error - Duplicate declaration in current scope" as it relates to Dim LR As Long.
I have attached my spreadsheet : Daily Treatment Summary.xlsm
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows()
Dim i As Integer
Dim intCounter As Integer
Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
[Code] ..........
I am trying to adapt a macro which deletes all rows in Worksheet 1 where the date in column K differs from a date in Worksheet 2. The date in Worksheet 2 will change each day.
I think the macro below could be used, but don't understand how to replace the "Value = ":70:" with a reference to the master date in Worksheet 2.
Sub test() Dim lr As Long, i As Long lr = Range("A1").End(xlDown).Row
For i = lr To 1 Step -1 If Cells(lr, 1).Value = ":70:" Then
Cells(lr, 1).EntireRow.Delete End If lr = lr - 1 Next i End Sub
I have six sheets in the work book, but one of them spills on to more than one sheet. If i print it or use print preview it displays all the sheets but when converting to pdf with below code only one sheets worth appears, then it displays the next sheet in the workbook, how to amend this so that if it spills over it all converts to pdf
The worksheet in question has 2,000 plus rows, and depending on what the end user selects, it can contain anything between 15 and 300 of those rows (it is a picking list). i currently have it set so that it prints to paper with five different sections for various product types and a summary page, each section has a named range and i have assigned macros to buttons such as immediately below.
Sub printoutsummary()
Range("printsummary").PrintOut
Range("printreturn").Select[code]......
I’m having difficulty writing a formula to solve the following dilemma:
I have “Sheet1” with staff details and each staff member may be listed more than once. I need to use columns C (StaffID) and L (Load). Load values may only be 1.0 or 0.5.
Then I have “Sheet2” which is being used to compile data from a range of sources – this is where I need to put my formula to get my answer. “Sheet2” has column A (StaffID) and each ID will only appear once on Sheet2.
The formula required needs to solve “if the StaffID from Sheet2 appears in Sheet1 (keeping in mind it may appear multiple times), sum the Load values for that StaffID provided the Load value on each line only equals 0.5".
If the StaffID doesn't match, the cell return is to be 0.
I will then need to do this again in another cell where the Load value only equals 1.0 (obviously just amending the condition value in the formula).
I am trying to print one of the Sheets in my Workbook by looking at a specific cell that can have one of the sheet names.
View 7 Replies View RelatedI have a sheet named PE which is the main source of handling a contents in other sheet.
How to make a selected contents in PE sheet appears automatically according to weeks in sheet1?
How do i use data validation, offset or combo box to solve this problem?
I'm making a Excel 2013 spreadsheet that has formula in a column that auto enters a number 1-40 when something is entered to the left of that cell. There are 300 rows in the spreadsheet. I would like to make a drop down list in a column cell to the right that would delete that number in that cell from the drop down list. For example cell C1 has 39, that 39 then is deleted from the drop down list. C2 has 22 in it, click on the drop down list cell and it shows 1-40 less 39 and 22.
View 11 Replies View Relatedwhen I open my sheet on another computer, all cell value which contain formula show no value. just blank. formula is link to same workbook on different tab. when I save as again on my desktop, all value appears. excel 2007 is using.
View 3 Replies View Relateda macro that will delete a line if the message #N/A appears. Right now I have a macro that will hide a row if it has a zero in it and it loops all the way till it hits "done" would like to add the ability to delete a row with #N/A in it, here is my code for the hide zero macro
View 9 Replies View Related
