VBA-搜索并删除重复项 [英] VBA - Search and remove duplicates

问题描述

我正在寻找一种我不具备VBA知识来编写自己的脚本的算法.所以我被卡住了.并不是因为我不费吹灰之力就去尝试了(再加上，这段代码是我更大的VBA代码的最后一部分)，我只是缺乏知识/经验/技能...

I'm looking for an algorithm for which I do not have the VBA knowledge to script myself. So I'm stuck. It isn't through lack of effort trying because I have given it a go (plus, this bit of code is the last remaining piece of my bigger VBA code) I simply lack the knowledge/experience/skill...

基本上，我有一个Excel文件.该文件中有一个工作表"sheet1". Sheet1包含许多数据行.在sheet1中包含的行数可以从1到n.有时，我可能有50个，有时我可能有30个，依此类推.这是与书的版式一致的，即，我在A列中有用于标识数据库中产品的代码.

Basically, I have an Excel file. In this file is a sheet, "sheet1". Sheet1 contains many rows of data. The number of rows contained in sheet1 can vary from 1 to n. Sometimes, I may have 50 while other times I may have 30, etc. What is consistent is the layout of the book, i.e. I have codes in column A which identify a product in my database.

我想做的是这样:

1..扫描工作表中的空行(由于工作簿的生成方式，有时我会有空白行)并将其删除.这些空白行有时位于包含数据的行之间，而有时可能会拖到工作表的末尾.

1. Scan the sheet for empty rows (due to the way the workbook is generated, I sometimes have blank rows) and remove them. These blank rows are sometimes in-between rows with data while at other times may be trailing at the end of the sheet.

2..删除空白行后，找到最后使用的行.将其存储到变量.我发现这段代码对于执行此操作很有用:

2. After removing the blank rows find the last used row. Store that to a variable. I have found this piece of code useful for doing that:

mylastrow = myBook.Sheets("Results").Cells.Find(what:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

3 .从(2)中确定的行开始，我要获取A(x，其中x = mylastrow)中的产品代码，并查找它的其他任何出现(在A列中) .如果找到任何行，则删除与之对应的整个行.重要的是，此循环必须反向.例如，假设mylastrow = 40，循环将需要从A40开始，而在下一次迭代中，A39将执行(如果已删除行，则返回38).这是因为，对于任何产品编号，该行中的相应数据在该列的更下方(因为生成表格的方式)包含更多数据.本质上，最接近最后一行的条目是最新的.

3. Starting from the row determined in (2), I want to take the product code in A(x where x = mylastrow) and find any other occurrences of it (in column A). If any are found, delete that entire row corresponding to it. Importantly, this loop must go in reverse. For example let's say mylastrow = 40, the loop will need to begin at A40 and on the next iteration do A39 (or 38 if a row has been removed?). This is because with any of the product numbers the corresponding data in the row contains more data further down the column (because of the way the sheet was generated). Essentially the entry closest to the last row is the most recent.

希望我已经能够正确地解释情况了.但是，如果不这样做，您愿意摆脱挑战(我的负担?)，我将不胜感激.

Hopefully I've been able to explain the situ properly. But if not and you're willing to take the challenge (my burden?) off me I would be very grateful.

QF

推荐答案

发展这种知识和技能的唯一方法是进入那里并编写代码！我敢肯定有人会进来并为您编写整个过程，但是与此同时，这些资源应该可以为您提供自行完成工作的工具.

The only way to develop that knowledge and skill is to get in there and code! I'm sure someone may come in and write you the entire procedure, but in the meantime these resources should give you the tools to do it yourself.

首先，在此处> 删除空白行.它依赖于选择"作为范围，因此您可以手动选择工作表的所有单元格，然后运行宏，或将其替换为以下内容:

First, check out the method here to delete blank rows. It relies on "Selection" for the range, so you can either manually select all the cells of the sheet, then run the macro, or replace it with the following:

Dim r as range
set r = Sheet1.Cells 'now use r instead of Selection

或(甚至更好)，使用您的代码查找最后使用的行，并将行1的范围设置为"mylastrow".

OR (even better) use your code for finding the last used row and set the range from row 1 to "mylastrow".

接下来，从"mylastrow"开始，开始将A列中的值添加到Dictionary对象(示例

Next, beginning from "mylastrow", start adding the values in Column A to a Dictionary object (example here). You can use a row counter to decrement from "mylastrow" to 1. Here's an example of how it would work. The key is assumed to be in the 1st column ("A").

Dim dict As Object
Dim rowCount As Long
Dim strVal As String

Set dict = CreateObject("Scripting.Dictionary")

rowCount = Sheet1.Range("A1").CurrentRegion.Rows.Count

Do While rowCount > 1
  strVal = Sheet1.Cells(rowCount, 1).Value2

  If dict.exists(strVal) Then
    Sheet1.Rows(rowCount).EntireRow.Delete
  Else
    dict.Add strVal, 0
  End If

  rowCount = rowCount - 1
Loop

Set dict = Nothing

之前: 后:

请注意，由于我们在rowCount为1时停止(假设有标题)，因此未触及第一行.

Note that the 1st row hasn't been touched since we stopped when rowCount is 1 (assumes there's a header).

这篇关于VBA-搜索并删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！