更新具有重复值的列表的列和行值 [英] updating column and row values of the list which has duplicate values

问题描述

  Dim masterSheet 
我已经合并了可变数量的工作表，这些工作表被称为任何东西，包括数据作为工作表
 
设置masterSheet = Sheets（Komko）
 
'用于保存主表的已使用范围的变量
 Dim usedRangeMaster As Integer 
 
 Dim ws As Worksheet 
 
'循环通过当前工作簿中的每个工作表
对于每个ws在工作表
 
'如果sheetname包含data（UCase将名称转换为大写字母）
如果InStr（1，UCase（ws.Name），DATA，vbTextCompare）> 0然后
 
'计算主表的使用范围
 usedRangeMaster = masterSheet.UsedRange.SpecialCells（xlCellTypeLastCell）.Row + 1 
 
'用于保存的变量使用子表的范围
 Dim usedRangeSub As Integer 
 
'计算子表的使用范围
 usedRangeSub = ws.UsedRange.SpecialCells（xlCellTypeLastCell）.Row 
 
'从子表单复制相关范围
 ws.Range（C1：C& usedRangeSub）.Copy 
 
'将复制范围粘贴在列中的使用范围之后a 
 masterSheet.Range（A& usedRangeMaster）.PasteSpecial 
 End If 
 Next ws 
  

此代码复制数据的C列，包括表格，然后将其粘贴到我的主表（即Komko）列A中。现在我想标识相同的值列C并删除先前匹配值的完整行。 （例如，如果C1的值为123，如果C7的值为123，则包含C1的整行将自动被删除）

我该怎么做？ / p>

解决方案

请参阅 http://analysistabs.com/vba/find-duplicate-values-column/ 找到重复的条目和删除Excel VBA中的一行，以获得一些想法如何以良好的方式删除该行。

合并列之后开始搜索C.如果需要，使用布尔变量跳过第一个找到的条目。从第一个链接替换代码 Cells（iCntr，2）=Duplicate，并放置删除行的方式。

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 ?

解决方案

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.

这篇关于更新具有重复值的列表的列和行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！