0

I have merged variable amount of worksheets which are called anything includes the word "data" through this code;

  Dim masterSheet As Worksheet

 Set masterSheet = Sheets("Komko")

 'Variable to save the used Range of the master sheet
  Dim usedRangeMaster As Integer

 Dim ws As Worksheet

  'loop through each worksheet in current workbook
 For Each ws In Worksheets

    'If sheetname contains "data" (UCase casts the Name to upper case    letters)
    If InStr(1, UCase(ws.Name), "DATA", vbTextCompare) > 0 Then

    'calculate the used range of the master sheet
    usedRangeMaster = masterSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1

    'Variable to save the used Range of the sub sheet
    Dim usedRangeSub As Integer

    'calculate the used range of the sub sheet
    usedRangeSub = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row

    'copy relevant range from the subsheet
    ws.Range("C1:C" & usedRangeSub).Copy

    'paste the copied range after the used range in column a
    masterSheet.Range("A" & usedRangeMaster).PasteSpecial
End If
Next ws

This code copies the C column of the "data" including Sheets and then pastes it to my master Sheets( which is called "Komko") Column A. Now i would like identify the same values in Column C and delete the complete row of the previous matching value. (e.g if C1 has value "123" and if C7 has value "123" the whole row which includes C1 should automatically be deleted)

How can i do this ?

Yigit Tanverdi
  • 161
  • 6
  • 18

1 Answers1

0

See http://analysistabs.com/vba/find-duplicate-values-column/ to find your duplicate entries and Delete a row in Excel VBA to get some ideas how to delete the row in a good way.

Start searching after you merged your column C. Use a Boolean variable to skip the first found entry if necessary. Replace the code Cells(iCntr, 2) = "Duplicate" from the first link and put your way of deleting the row there.

Community
  • 1
  • 1
freakfeuer
  • 98
  • 1
  • 8