Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete duplicates and add worksheet name
Hello Programmers..
I have a problem with some macro's i found. I have been searching for day's now but could not find a good solution for my problem. A lot of threads are almost good but because of my low knowledge of programming i am not able to adjust the macro according to my needs. First i will explain the situation: I have a excel sheet with about 20 worksheets. There are two "intro" sheets with all sorts of information (they called "info" and "archive") The rest of the sheets are project related sheets. In these project related sheets are about 20 columns and 50 rows. The data that are in these sheets are a download from SAP. Problem: The download contains duplicate numbers in column J. Solution: I found a few macro's that are almost good: ------------------------------------------------------------------- Sub DeleteDuplicates() Dim X As Long Dim lastrow As Long Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets lastrow = Range("J65536").End(xlUp).Row For X = lastrow To 1 Step -1 If Application.WorksheetFunction.CountIf(Range("J1:J" & X), Range("J" & X).Text) 1 Then Range("J" & X).EntireRow.Delete End If On Error Resume Next 'Will continue if an error results ws.Range("A1") = ws.Name '*********************** Next X Next ws End Sub ------------------------------------------------------------------- The above macro will delete all the duplicates BUT it is NOT looping through all the the worksheets in my workbook (except for the two mentioned above). What is wrong with my macro??? I would also (if its possible) like to add the worksheetname automaticly according to input of Cell A10 I would apreciate if someone can help me! Thanks |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicates, sum column then delete duplicates | Excel Programming | |||
Delete Duplicates | New Users to Excel | |||
how do i find and delete duplicates in excel worksheet? | Excel Discussion (Misc queries) | |||
delete duplicates macro to color instead of delete | Excel Programming | |||
delete duplicates | Excel Programming |