VBA-删除不包含特定单词的行 [英] vba - deleting rows that don't contain specific word

问题描述

我正在尝试删除Q列中不包含特定单词的行，并重新计算总和行.现在，这是我正在设计的代码的一部分，因此，由于其余代码都可以正常工作，因此我只发布此部分.

I am trying to delete rows that don't contain specific word in column Q and re calculate the sum row. Now this is part of the codes that I am designing so I am only posting this part since the rest of codes work just fine.

对于我的数据，我有一个引用列表及其信息，并为它们对应的区域(列Q)添加了一个列，因此我想过滤要分析的区域.对于总行，我需要重新计算数字，因为它只能反映该区域中的总和.

As for my data, I have a list of cites and their info and I added a column for their corresponding region (column Q) so I would like to filter the region I want to do analysis. For the total row, I need to re-calculate the numbers since it should only reflect the sum that are in the region.

我的数据范围已合并标题和总行，因此不确定是否会使情况复杂化.

My data range has merged headers and a total row so not sure if this will complicate the situation.

Sub test()

Dim Rng As Range
Dim x, lngLastRow As Long
Dim ws As Worksheet

Set ws = Worksheets("A")
lngLastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("Q5:Q" & lngLastRow - 1)
For x = Rng.Rows.Count To 1 Step -1
If InStr(1, Rng.Cells(x, 17).Value, "NW") = 0 Then
Rng.Cells(x, 1).EntireRow.Delete
End If
Next x
End Sub

现在，我当前的代码只是删除除合并标头之外的所有内容，因此我需要帮助来过滤每个区域.您会发现我还没有重新计算求和行，因为到目前为止我的代码还没有工作，所以我还没有添加那一部分.

Now my current codes just delete everything excepts the merged header so I need help to filter for each region. And you can see that I haven't re-calculate the sum row as well since my codes have not worked so far so I haven't added that part yet.

现在，我尝试的第二种方法是使用过滤器功能，它给了我"Range类失败的运行时错误1004 AutoFilter方法"，这是我尝试过的代码.

Now, second method I have tried is to use filter function and it's giving me "Run Time Error 1004 AutoFilter method of Range class Failed" and this is the codes I tried.

   With ws.Range("B5", "Q" & lngLastRow)
   .AutoFilter
   .AutoFilter Field:=17, Criteria1:="NW"
   End with

所以我不知道哪种方法会更好，或者你们是否有不同的方法.

So I don't know which way will work better or if you guys have a different approach.

这是我的数据显示.

推荐答案

您需要将以下代码行中的列值更改为1，而不是17，如下所示:

You need to change the column value in the line of code below to 1 instead of 17 like so:

If InStr(1, Rng.Cells(x, 1).Value, "NW") = 0 Then

这是因为您正在访问先前设置的范围内的单元格，该范围只有1列:Q列.因此Q列是Rng变量的第一列.第17列是您范围(Q列)中第一列(即AG列)的17列.如果您将代码写为ws.Cells(x, 17).Value，则代码将选择正确的列，因为那将是电子表格的第17列，而不是您设置的范围的第17列.

This is because you are accessing the cells within the range you set previously, which only has 1 column: column Q. So column Q is the first column of the Rng variable. Column 17 is 17 columns from the first column in your range (column Q), which is column AG. Your code would be selecting the correct column if you had written it as ws.Cells(x, 17).Value because then that would be the 17th column of the spreadsheet, as opposed to the 17th column of the range you set.

这篇关于VBA-删除不包含特定单词的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！