Over 1 million tech questions and answers.

Solved: Im sure it can be done.. Delete Duplicate Rows

Q: Solved: Im sure it can be done.. Delete Duplicate Rows

Ok another challenge.. I wish I could do this myself, but I struggle with the code.

Is there a way to have a macro check the line below it, and if it is a duplicate row, delete it

RELEVANCY SCORE 200
Preferred Solution: Solved: Im sure it can be done.. Delete Duplicate Rows

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Im sure it can be done.. Delete Duplicate Rows

Read other 16 answers
RELEVANCY SCORE 80.8

Simple problem - copy sheet then delete duplicate rows

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.

Thanks for your help. I am a newbie to VBA.
 

A:Simple problem - copy sheet then delete duplicate rows

stewartrr said:

I am a newbie to VBA.Click to expand...

Not a problem, since you don't need it for this ; a combination of built-in functions will do.

It's not clear if you have blanks in col A -- if you do, that can be easily fixed.

See the attached. The list of ID #s in col A of Sheet2 was extracted from Sheet1 using Advanced Filter (unique records only). The formulas in col B of Sheet2 pull in the value from col B of Sheet1 (per the last row for each ID #).

Post back if you need help interpreting the formulas.
 

Read other 1 answers
RELEVANCY SCORE 80

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.
 

A:XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Read other 11 answers
RELEVANCY SCORE 71.2

I have been working on a project for a few weeks and I could really use some help. I'm attaching a dummy file (example1) that is an example of how my XLS comes to me each month.

I need a macro that leaves me with the second file (example2). If the macro could just grab the Student name, then put the following info for each student all on 1 row, that would be perfect:

Child's Name | Date In | Time In | Rounded | Time Out | Rounded

Sometimes for some reason there is a student with a (Cont) after their name. I need that row deleted and just pull the times up onto the same row as other times. The example2 file might make sense.

Here are the steps I am doing manually that I hope a macro could do for me!
Un-merge cells: B10
Un-merge any cells with a child's name such as Doe, Jane
Delete any row with a childs name that has (Cont) after it such as Doe, Jane (Cont)
Delete rows: 1-9
Delete columns: A, C, F, H, I, J, M, N, O, and P
Manually move all info up to one row per student
Delete all empty rows below
See example2 as the completed file but I only moved a few of the rows per student for the sake of time.

Is there any way to do all of this with a macro?
 

A:Solved: XLS Macro to delete specific rows/columns and move columns to rows.

Read other 16 answers
RELEVANCY SCORE 64.4

Hi All,

I have a spreadsheet containing 30 address blocks that I'm trying to clean up. A sample is attached.

All of the data is in column A. Each address block is 6 rows, like this:

Name
Company Name
Street Address
City/State/Zip
Phone number
Email Address

Between each 6 row block is a varying number of additional rows that might or might not contain extraneous information. I want to remove those extra rows leaving only the address blocks.

I'm thinking about counting the "x" number of rows between email address "@" symbols, and then just deleting X-6 rows. But I'm not sure how to accomplish this.

Any ideas how this might be accomplished, or any better suggestions to accomplish the goal?
 

A:Solved: Excel macro/vba to delete rows

"might or might not contain extraneous information"

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, "@") <> 0 Then
Cells(Cell.Row - 5, 1).Resize(6, 1).Copy Range("B" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell

If those are real names and addresses, you should remove your file.
 

Read other 2 answers
RELEVANCY SCORE 64.4

Afternoon everyone,

I've got a question. I created this code, but I get the error: "Block If without End If". The idea is that the administrator can delete multiple records (rows) based on what he selects, using check boxes.

I put this code:
Code:
Private Sub DeleteRecords_Click()
If CONTACTS.Select Is Not Null Then
RunCommand acCmdDeleteRows
End If

 

A:Solved: Delete multiple records (rows)

Problem fixed,

used the wrong syntax for the object and IF command.
 

Read other 1 answers
RELEVANCY SCORE 64.4

I am trying to delete rows where the value in the 4 column is less than 21. I have tried variations of the code below but all do the following:

Delete some rows that are less than 21 and leave others
Delete some rows that are greater than 21 and leave others

When I step thru the code and check the value it shows me the value in the cell and deletes whether it is true or false. Please tell me what I am doing wrong.

LastRow = ActiveSheet.UsedRange.Rows.Count
For r = LastRow To 1 Step -1
With Cells(r, "D")
If .Value < "21" Then .EntireRow.Delete
End With
Next r
 

A:Solved: Excel VBA Delete Rows by Value Not working

Duh! Don't need " around 21 and changed to

LastRow = ActiveSheet.UsedRange.Rows.Count
For r = LastRow To 1 Step -1
If (Cells(r, 4).Value < 21) Then Cells(r, 4).EntireRow.Delete
Next r
 

Read other 1 answers
RELEVANCY SCORE 64

I have a spreadsheet with around 3000 rows. There are some duplicates. Is there an easy way to find the duplicates in excel and export them to another sheet for review, or even highlight or separate them in some manner?
 

A:Duplicate rows

Read other 7 answers
RELEVANCY SCORE 63.6

Hi All,

I am in need of help! I am trying to create a macro that: deletes rows in what looks like a "page break" to combine data spanning 100K+ rows.

For some reason, after a variable amount of rows of data there are 7 lines that reappear in what looks like a "page break." I say that it looks like a page break because at the 3rd row of this "break" shows the date and then the 6th row shows all the column headers again. They always appear in this order:
1: Blank Row
2: Blank Row
3: Date in Column A
4: Blank Row
5: Blank Row
6: Column Headers at Column B
7: Blank Row
8: Data (onwards until the next "page break")

So what I'm trying to program is a macro that goes through the data to delete all of these breaks until the end of the spreadsheet. I included an image of what the whole dataset looks like:

The background info to the data is below:
Column A is blank except for the date. The date shows in A1 and then on the 3rd row in every page break. The data starts in Column B until the end of the data set in column AI.

I would REALLY REALLY REALLY appreciate anybody's help with this. If anyone can get me started that would be great. I was thinking about recording the macro as I go to the first line of data, hit CTRL+DOWN, highlighting the seven rows, hitting CTRL+RIGHT, deleting the rows (doing all up to this point only once), and then making the macro loop until the end of the data set.

What do you experts think?
Thanks Al... Read more

A:Solved: Excel 2007 VBA - Find Rows and then Delete them

Read other 15 answers
RELEVANCY SCORE 63.6

I am trying to find a way to find a specific text within a worksheet, select that cell and the 9 rows under that row, and delete the 10 rows.
 

A:Solved: Excel Macro -> Delete Multiple Rows

Read other 16 answers
RELEVANCY SCORE 63.6

Hello,
I would like to ask for help regarding a template I have. I am really new to macros and i hope that i can be helped here.

I have a file that has two tabs where i would need a macro to do the same thing except that basis for deletion would be on different columns. Example, for tab 1, I have columns A-W. W contains a formula which would indentify whether the row would need to be deleted or not. For tab 2, I have columns A-S. S contains the formula that would indicate whether the row would be deleted or not.

Currently i have this(Specifically for Tab A):

Sub DEL_K_A_C()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet.Select
Firstrow = 10
Lastrow = 10000
For Lrow = Lastrow To Firstrow Step -1
With Cells(Lrow, "W")
If Not IsError(.Value) Then
If .Value = "DELETE" Then .EntireRow.Delete
End If
End With
Next Lrow

End With
With ActiveSheet.Select
Range("W2").Select
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Logically i would only need to change "With Cells(Lrow, "W")" to "With Cells(Lrow, "S")" for tab 2. Oddly, it refuses to work. Specific macro for Tab 1 would work, when i move to tab 2 and call on the macro for that tab, it doesnt work and continues on, not stopping.

What shou... Read more

A:Solved: deleting rows marked as delete in column s

lynn_plazo,

Will the "Tabs" have names?
 

Read other 3 answers
RELEVANCY SCORE 63.6

Hi,

I have a worksheet that contains 2 columns of data. I have listed a sample of the data as below column A and column B

...............Column A....................................Column B
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table
Row 5......bigtalltree.net...............................big tall tree

I have some questions on how to do the things I want.

1) I want to excel to search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole rows.

results to return will be
Row 3......thebigshortdog.net........................the big short dog

2) I want to search Column A only, delete the rows if the cells in Column A ends with '.net'

results to return will be
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse

3) I want to detect cells that contain the word tall. If the word tall is detected in Column A or Column B, delete the row.

results to return will be
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big bla... Read more

A:Solved: Delete rows when specific words are found

Read other 16 answers
RELEVANCY SCORE 63.2

I have Win XP, Excel 2003, and I want to delete all, not just duplicate, rows in a column of numbers. Example:
101
102
103
104
105
102
This one last entry (102) is duplicate. I want this 6th row deleted AS WELL AS the 2nd row so the only ones left are the numbers that are unique. Is there a formula I can throw in?
 

A:Deleting all duplicate rows

Read other 12 answers
RELEVANCY SCORE 62.8

I need to delete the first 12 rows from 180+ seperate spreadsheets. Whats the easiest way to do that?
 

A:Solved: Excel 2007 Delete Rows In Multiple Files

I found a macro that works. It deletes the rows beginning with what you are looking for, up to the first non-blank row which works for my purposes.

Sub ProcessFile()
Dim Rw1 As Long, Rw2 As Long
Set xSheet = Sheets("Sheet1")
This = "-------------" ' change this to what you need to match
On Error Resume Next
Rw1 = Cells.Find(What:=This).Row
Rw2 = Cells.Find(What:=This).End(xlDown).End(xlDown).Offset(-1, 0).Row

xSheet.Rows(Rw1 & ":" & Rw2).Delete Shift:=xlUp

End Sub
 

Read other 1 answers
RELEVANCY SCORE 62.8

Good Day,

I would like to copy the rows in a worksheet to another worksheet where a value in the row say H2 <900. I would then like to delete those cells from the former worksheet. Can you assist with this. Thanks
 

A:Solved: Macro to delete rows where cell meet a condition

Read other 7 answers
RELEVANCY SCORE 62.4

Hello Again

This is an Excel question. I have many rows in an Excel spread sheet that have duplicate data. These duplicate rows vary in content and in length each and every day. So far I have been deleting the duplicate rows manually. This task is now becoming time consuning. To make it easier on myself, I do a sort so that duplicates are all clumped together. For example I may the following in Columns A and B. Note, there are no spaces between different products.

000-00-001 product A
000-00-001 product A
.
.
.
000-00-002 product B
000-00-002 product B
.
.
.
etc and so on

I tried writing a macro to eliminate the duplicates only but it does not work properly. Can you help me on this one?

Mario
 

A:Deleting Rows that have duplicate data

Read other 6 answers
RELEVANCY SCORE 62.4

This is an example:

I have multiple rows in a very large spreadsheet (4MB) and want to delete the duplicates so I am only left with Apple, Orange, etc. The auctual data will be based on a Name column and two Address columns.

Thanks so much for even considering helping !
 

A:Remove duplicate rows in Excel

Maybe this will help

http://www.cpearson.com/excel/duplicat.htm
http://www.excelforum.com/showthread.php?t=341156&highlight=duplicate

Rollin
 

Read other 2 answers
RELEVANCY SCORE 62.4

I have data in 1 row that I need broken into additional rows. If you look at the example any row that has a 2 or greater in the ULnct column I would need broken into additional lines. Example if there was a 2 listed i would need this line to repeat 2 times, 3 - 3 times and so on. This way i can use a count identifier for each row. so in this example I would have 7 rows instead of 5 so my count would be 1 to 7.
 

A:Access - create duplicate rows

Read other 14 answers
RELEVANCY SCORE 61.2

Hey all, I need your help!

I am currently running Office 2000 (But have access to 2007) on XP and I have a bit of an issue that I hope you can help with.

Right now since a system upgrade to a new software, it requires me to do ordering for our customers by manually searching every part number by hand. Unfortunately this means using old sales statistics, which means I have to keep an old excel file around with the sales figures. Here is where I need your help:

We change vendors pretty often with part numbers, and that means I have to do a run off of what numbers that we are using every month I make the order. Normally this wouldn't be so bad but I need to keep the sales figures, and that means every month I need to manually combine the spreadsheets, sort by product number, and then delete the duplicate lines by hand so that I can have the new numbers mixed in.

I have tried a few VB scripts I found floating around, however this does not solve my issue. Since it will delete just the duplicate text and not the entire line. I have heard that Access might be able to help me out with this however I have no experience with it and cannot try it out.

If this sounds confusing at all, let me know and I will try and make it more smooth, but for now I need to get back to deleting some lines...
Thank you very much for your help!
 

A:Merging Excel files and deleting duplicate rows?

Read other 8 answers
RELEVANCY SCORE 60.8

Hello,
I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
Application.ActiveSheet.UsedRange
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

Read other 6 answers
RELEVANCY SCORE 60

For various reasons, I find that a large number of my music tracks in iTunes are duplicated. I wanted to delete these but don't relish trawling through about 3000+ tracks in the "My Music" section and deleting the unwanted ones individually! Is there any way (duplicate tracks deletion program, etc.) that I can use to help me to do this? Once I have rid my system of these I would then like to transfer most of the remainder to Playlists, which I could easily arrange. Thanks.
 

A:Solved: How to delete duplicate tracks in iTunes

Have been told about a small free program called GizmoTrim which cleared out all my duplicate tracks very quickly and successfully - highly recommended! (Marking solved)
 

Read other 1 answers
RELEVANCY SCORE 60

I have a list of 39525 emails in column A and a list of 19909 emails in column B. If there is the same email in column A and B, I need the duplicate removed from column B and stay in column A. Please help Thanks!
 

A:Solved: Delete Duplicate Emails In One List of Two

Excel 2010
 

Read other 1 answers
RELEVANCY SCORE 60

I began receiving messages about corrupted files in XP and so tried to repair option to reinstall the corrupted files, but somehow ended up completely reinstalling into a second directory.

Both directories are on my C drive. One, the original, is still the same. All software installed and working the same as before my attempt. The second, Windows.0, is XP with nothing installed, fresh condition.

As this isn't an upgrade from another windows, I'm don't know how to get rid of this second windows.

Also, everytime I reboot my computer, it asks me which windows I want to use.
Not to mention the wasted disk space...

How can I get rid of these!!??

A:[SOLVED] How do I delete a duplicate copy of winXP?

Hi J-BO and welcome to TSF.

Try This

Read other 5 answers
RELEVANCY SCORE 59.2

Hi.
I am using an Excel worksheet with several thousand records listed. On a daily basis, I get an updated list, but I need to keep hold of some of the 'old' records.
At present I'm copying the new list, adding it to the end of my existing list and then filtering it so that only unique records are visible.
Obviously, this means the list is ever-increasing with many duplicated records (hidden or otherwise).
Is there a way to simply delete the duplicate records (so I can just keep one record of each) rather than hide them?

Ta
Gram
 

A:Solved. Excel 2000 - delete duplicate records?

Read other 15 answers
RELEVANCY SCORE 59.2

I do a weekly report that I base on results that I get with search done in Internet Explorer. I can then cut and paste these results into an Excel spreadsheet, but I need to delete most of the information (whole rows) and reorganize the remaining rows by taking the even numbered rows and putting them into the B column, then delete that Row as well. I have included some images that should help. A few more items:

1.)The information that I paste into the excel spredsheet may contain up to, but no more than 350 lines from the original copying source in Explorer (I'm not sure how many lines that translates into Excel)

2.) After every 20 records in Explorer there is a "top" link that gets copied, which needs to be taken into consideration when deleting the extra rows entirely

3.) Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits, as well as the rows that contain the price with the $ For instance the first record in my example I ONLY kept 8055312 11 & $70,000. The price will ALWAYS have a $ and the first set of number will ALWAYS have 7 digits first, no letters.

Attached is a jpg that shows the various steps, the last screen shot has a few of the cells highlighted in the upper left hand side. Those 6 highlighted cells is all of the information I need, and how I need it presented from the first 3 search records, the MLS number and the price.
Thanks!
 

A:Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns

Read other 16 answers
RELEVANCY SCORE 58.8

Is there such a thing,or is this one of those times when I have to go through each file and delete by hand? Date modified will not apply since I made tag modifications,my bad.That is a whole new problem.
Jeebers I really messed up my music files.The transfer from the old comp 2 HDD's to the new comp ,1HDD,had 2 "my music" folders,one under docs & sets,my music, and another one under docs & sets, my name ,my docs,my music.....Put them in 1 folder and now have 2 of almost all ,except for all the new tunes I have put in that folder.
I just need to know if there is a best one time only use for a reliable duplicate file removal program.
 

A:Solved: Good,Better,Best Duplicate Music File Delete Program

Read other 9 answers
RELEVANCY SCORE 56.8

Hi guys, I have this macro which is below and what i need it to do is only delete the rows that do no contain the number "6" in it. i have over 30k lines many which are either blank or have other values. I only want to keep the rows that contain the value 6 in the C Column.

Sub DeleteDuplicates()

Dim x As Long
Dim LastRow As Long

LastRow = Range("C30000").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("C1:C" & x), Range("C" & x).Text) > 1 Then
Range("C" & x).EntireRow.Delete
End If
Next x

End Sub

If you need a sample i can attach one.
Thanks ahead of time.
 

A:Solved: excel macro to delete rows not containing "6"

HI Chris,

Can you try this codes mentioned below , i have used auto filter for deletion of rows , but u need to mention the column number in the field and also the entire range of the sheet.

Sub DeleteDuplicates()

With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
'.Range("I2:I" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr
.Range("$a$1:$c$" & .Rows.Count).AutoFilter Field:=3, Criteria1:= _
"<> 6"

Set Rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not Rng Is Nothing Then Rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

End Sub

Hope its helps you...
 

Read other 3 answers
RELEVANCY SCORE 55.6

How would I go about geting a macro that will allow me to delete any row in a range that contains a zero value. I have a range "A6:C1003" and there are rows where all three columns contain zeros which I need to delete. For example columns A,B and C row 45, 48, 98 contain zeros.

The rows which contain zeros is not always constant so I need to have it somehow loop through the range and remove the rows completely.

Thanks
 

A:Delete zero value rows

Create a SQL statement that adds up the column values and if they eqal zero they should be deleted.
 

Read other 1 answers
RELEVANCY SCORE 55.2

I'm trying to delete the first 5 rows of every sheet except Sheet 1, and delete the last row of every sheet except sheet 1 (sheets are different lengths).
Can anyone steer me in the right direction? I've tried google searching for scripts that might work but when I piece them together I get errors everywhere.

Frankenstein code I pieced together:

Code:

Sub DelRows()

Dim Wks As Worksheet

For Each Wks In Worksheets
If LCase(Wks.Name) <> "Sheet1" Then
CurrentSheet.Range("a1:a5").EntireRow.Delete
End If
Next Wks

End Sub
 

A:Solved: Delete top 5 rows from every sheet except for Sheet 1

xmmx,
Try this.
Code:

Sub Delete_5Rows()
Dim wsheet As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
For Each wsheet In wb.Worksheets
With wsheet
If wsheet.Name <> "Sheet1" Then
wsheet.Activate
With ActiveSheet
wsheet.Range("a1:a5").EntireRow.Delete
wsheet.Range("A65536").End(xlUp).EntireRow.Delete
End With
End If
End With
Next

End Sub
 

Read other 3 answers
RELEVANCY SCORE 55.2

I have some code that pulls data from two different worksheets onto one worksheet. The data looks like the following:

WS1

123
123
852
9637
5789
5789
159

WS2

123
123
852
9637
5789
5789
159

WS3

123
123
852
9637
5789
5789
159
123
123
852
9637
5789
5789
159

I need to come up with some code that will go through the A Column to delete the rowa that contain duplicates. So, once the deletion code has ran, WS3 should look like this...

WS3 (After Deletion Code)

123
852
9637
5789
159
123
852
9637
5789
159

Does anyone have any ideas? I have the code below, but it works only if data is pulling from one worksheet.
Code:

LastRow = ws.Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(ws.Range("A2:A" & x), ws.Range("A" & x).Text) > 1 Then
ws.Range("A" & x).EntireRow.Delete
End If
Next x
 

A:Excel -> Delete Rows

Forgot to mention that the deletion code needs to be universal. There will not always be data from WS2, so the code I posted above will work. I just need help creating a code that will work if there is data in WS2.
 

Read other 1 answers
RELEVANCY SCORE 55.2

Hi there

I found this macro on the internet but it only deletes one row, even though I select a range in the input box. See attached workbook and run the macro to see what I'm talking about. Can you fix the macro so that when I select a range it deletes the entire range and not only one row?
Thanks
 

Read other answers
RELEVANCY SCORE 55.2

I want to write a script to to delete the top two rows of an excel file as part of a vbs file.
 

A:VB Script to Delete top two rows

something like this to open

Code:

Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.WorkBooks.Open("C:\Scripts\test1.xls")
Set objWS = objXL.ActiveWorkBook.WorkSheets("test1")

I am not sure how to delete the row. Do a google search, you should be able to find something
 

Read other 2 answers
RELEVANCY SCORE 54.8

I'm trying to remove duplicative data from cells in a column based on information from another cell.

Example:

Col A Col B Col C Col D
Name1 Dept 1 Salary 1 Bonus 1
Name2 Dept 2 Salary 2 Bonus 2
Name2 Dept 3 Salary 3 Bonus 2
Name3 Dept 4 Salary 4 Bonus 3

In the above, there are 2 separate entries for Name2 because they were in 2 separate depts. My source data lists the bonus amount (Bonus 2) paid to Name2 but lists it twice incorrectly and I need to remove the duplicate so it should look like this:

Col A Col B Col C Col D
Name1 Dept 1 Salary 1 Bonus 1
Name2 Dept 2 Salary 2 Bonus 2
Name2 Dept 3 Salary 3
Name3 Dept 4 Salary 4 Bonus 3

Any help or ideas would be outstanding.
 

A:Removing duplicate data in a table without removing rows in Excel

Read other 6 answers
RELEVANCY SCORE 54.4

I am trying to delete the rows in excel 2003 that wash each other (Debit - Credit =0). There is around 15000 lines and do not want to delete manually. I have attached a sample of the data. I added a column to pull only the first 9 characters from the Item column and added another column so the debits and credits were in one column. Then I subtotaled by the column with the first 9 characters. I need to delete the rows that make up any subtotal with a zero balance. Any ideas on how to do this?
 

A:delete rows in excel per criteria

So in your sample workbook you want the ARGTRUMI records removed completely and want to keep the other records since they don't balance out? Will the similar items be grouped together as shown in your example?

Rollin
 

Read other 2 answers
RELEVANCY SCORE 54.4

I have the following code, but when I run it, I get "Cannot use that command on overlapping selections" and the Selection.EntireRow.Delete line is yellow.

Sub DelBlankRows()

Columns("D").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete

End Sub

There is no Center and Merge on Column D, but there is in Column C. Is that potentially causing this issue?

-Stradt23
 

Read other answers
RELEVANCY SCORE 54

Hi,
can any body suggest me a macro code to delete the blank rows from the worksheets, i have a worksheet in which blank or empty rows are present unevenly. and i want to delete all such blank rows all at once. so please suggest me a code.
 

A:how to delete blank rows in a worksheet by macro?

Hi,
I referenced this utility at MSDN, and tested it.
This may or may not be what you are looking for
============== =====================================
Place the following VBA code in the worksheet you want to delete blank rows from
********************************************************************************
' The following code deletes blank rows from the active worksheet.
Dim Counter
Dim i As Integer
Sub DelRow()
' Input box to determine the total number of rows in the worksheet.
Counter = InputBox("Enter the total number of rows to process")
Activecell.Select
' Loops through the desired number of rows.
For i = 1 To Counter
' Checks to see if the active cell is blank.
If ActiveCell = "" Then
Selection.EntireRow.Delete
' Decrements count each time a row is deleted. This ensures
' that the macro will not run past the last row.
Counter = Counter - 1
Else
' Selects the next cell.
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub
================================================================================================
The solution can be found at.....
http://support.microsoft.com/kb/110759
**********************************************
You may further be able to programmatically create a form with a button that will automate the process.
Hope this helps
 

Read other 3 answers
RELEVANCY SCORE 54

I am using an excel sheet and I need to delete any rows that do not match the criteria I need:

688106
midvale
CITY TOTAL:
7891234
688123
SINGLE 01

In this column I would need to be able to automatically delete any row not containing SINGLE 01 or CITY TOTAL:
I have tried several different macros and cannot seem to make one work.
The actual column is 6000 rows long

Any help???
 

A:delete rows not matching multiple criteria

Read other 15 answers
RELEVANCY SCORE 54

I have to run these reports at work that contain UPC numbers. And sometimes, with how things work, I have UPC numbers, and their characteristics, that I have already reviewed. For a short example, UPC# 123456789 has certain characteristics associated with it. It is on my last week's report, so i have already reviewed it. UPC#0000000000000 and 11-111111-11111 and 123456789 are all on this week's report. I need to review UPC#0000000000000 and 1111111111111, but since i have already reviewed 123456789, I want to delete it, and all of the rows that contain its characteristics.

Needless to say, when I run into this issue, I end up needing to delete about 50 or 60 UPC #'s and their characterisitics. As you can see in the attached worksheet, there is alot of data under each UPC#.

So what I want to be able to do is search the entire sheet, for what is in column J, which is where i list my UPC's to delete. Once i find that UPC number(oh and the UPC is not going to be in a cell by itself, but the cell contains the UPC # somewhere in it) I want to delete all of the characterisitics that are associated with that UPC. I have hightlighted in my example what i would like to delete, after each find is completed, which includes the last row, which is empty. Also, column J will contain more than one UPC, i just used only 1 to keep the example simple.

Any help on this would be greatly appreciated!
 

Read other answers
RELEVANCY SCORE 54

Good morning,

I have a spreadsheet that itemises 1000's of transactions from different customers. It grows by the day - I have a macro that deletes the records I don't want to see based on the Customer name (delete row if <> to "ABC CO." etc.). What I would like to know is is there a way of doing this without looping through every line as it's taking longer to run every day.

Hope my explanation is sufficient.

Many thanks in advance.

Moll
 

A:Delete rows based on criteria - without looping

Read other 16 answers
RELEVANCY SCORE 53.2

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

Delete column C
Delete all rows without account numbers (Acct numbers will always be in column A)
Delete all rows that have dates in column F other than &#8220;00/00/0000&#8221;.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?
 

A:Excel VBA Macro to delete rows/columns with criteria

To delete column c, use:
Code:
Columns("E").EntireColumn.Delete
Delete all rows without account numbers (Acct numbers will always be in column A)
and
If Cells(y, "A").Value = "" Then Cells(y, "A").Delete shift:=xlUp

Use:
Code:
lastrow = cells(rows.count,1).end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, "A").Value = "" Then Rows(y).EntireRow.Delete
If Cells(y, "F").Value = 00/00/0000 Then Rows(y).EntireRow.Delete ' Delete all rows that have dates in column F other than 00/00/0000.
Next y


 

Read other 1 answers
RELEVANCY SCORE 53.2

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

Delete column C
Delete all rows without account numbers (Acct numbers will always be in column A)
Delete all rows that have dates in column F other than 00/00/0000.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?
 

A:Excel 07 MACRO to delete rows/columns w/ criteria

Delete all rows without account numbers (Acct numbers will always be in column A)Click to expand...

If there are no acct numbers, will there be

blanks ""
Space " "
dash -
other ??
Can you provide a sample with dummy data so we can see the data structure?
 

Read other 1 answers
RELEVANCY SCORE 53.2

Hi
Is there a quick way to delete blank rows in an excel worksheet. I am using excel 2007

Thanks
Brian
Dubli, Ireland
 

A:Delete blank rows in an excel worksheet 2007

Read other 9 answers
RELEVANCY SCORE 52.8

I would like to get a script going that checks column A in an Excel spreadsheet with the contents of another file. The other file can be separate Excel file or a txt file. The script would delete the entire row of data in the main spreadsheet when the data (string) in column A matches anything listed in the txt (or other Excel) file.

I'm a real beginner and appreciate any help you can give me.
 

Read other answers
RELEVANCY SCORE 52.8

I have selected certain cells, e.g. total cells, and cells that contain formulas which should not be changed, and 'locked' them. I then protected the worksheet but allowed users to insert and delete rows/columns. However, when I open the spreadsheet, the option to insert or delete in the context menu is greyed out. I can insert entire rows, but I have multiple sections adjacent to eachother which will become misaligned if entire rows are inserted.

Any idea how allow users to insert and delete rows and columns which span a specific number of cells?

Thanks
 

A:In Excel, I can't insert/delete rows in a protected worksheet (even when enabled)

Read other 6 answers
RELEVANCY SCORE 52

I have the following data that i need to create a set of rules or code that if multiple criteria is met to delete the row. I have included some examples of what i need. i tried doing a Recording macro but didnt work since sometimes some of the criteria is not there at the time of running the macro and it fails.

I need to have code look for specific values, i.e.

Account paid Y
Date of Payment Blank
Highlight all cells
'this is to review information
after this then check if it has Code 3 = C1 and Code 4=D1
Highlight all cells
'this is to review information

After
Check if Code 1=B2
Check if Paycode is blank or other
Highlight all cells
'this is to review information
Then delete other rows



AccountBalanceDateCode 1Code 2Due dateCode 3Code 4Code 5Account paidPaycodeJonnathan5009/1/2011A1B1C1D1E1YCollectedJessica252.359/2/2011A2B2C2D2E2YCollectedMaria321.549/3/2011A3B3C3D3E3NPayPlanLeon652.339/4/2011A4B4C4D4E4NPayPlanDavis525.329/5/2011A5B5C5D5E5YCollectedCarmen277.679/6/2011A6B6C6D6E6NOtherKarina346.869/7/2011A7B7C7D7E7NJose 677.659/8/2011A1B1C1D1E1YCollectedMary550.649/9/2011A2B2C2D2E2YPayPlanMiguel302.999/10/2011A3B3C3D3E3NPayPlanJessica372.189/11/2011A4B4C4D4E4NCollectedElaine702.979/12/2011A5B5C5D5E5YOtherPatricia575.969/13/2011A6B6C6D6E6NCollectedKaren328.319/14/2011A7B7C7D7E7NCollecteddavid397.59/15/2011A1B1C1D1E1YPayPlanJoe728.299/16/2011A2B2C2D2E2YPayPlanJoseph601.289/17/2011A3B3C3D3E3NCollectedwilliam353.639/18/2011A4B4C4D4E4Nbill422.829/19/2011... Read more

A:MS Excel 2007 - VBA or Macro to delete rows based on multiple criteria.

Read other 8 answers
RELEVANCY SCORE 52

I have data that repeats hundreds of times. I am trying to put each string of data on 1 line.

f-name(A2) initial(B2) l-name(C2) ID#(D2) DOB(E2) Street(F2) company(G2)
City(F3) company#(G3)
State,Zip(F4)
Home Phone(F5)
Work Phone(F6)
Email(F7)

After the data is moved to one line I would like to delete the lines that previously housed the information. The company# can be deleted. I have never worked with macros. I would appreciate any help given.
 

A:Solved: move data from rows and columns to other rows and columns

bump
 

Read other 3 answers