vba删除不包含在范围中定义的设置值的行 [英] vba deleting rows that do not contain set values defined in range

问题描述

我有一张25k行的数据。我需要在整个工作表中搜索我在标签2上命名的范围中定义的某些字词，称为KeywordSearh。该范围包含我需要查看主要数据的单词列表。我想删除不包含这些关键字的所有行（并将所有保留行向上移动），并仅保留参考关键字（包括标题行）的行。关键字可以写成任何单元格内的文本，它还将包含其他文本，因此，搜索函数需要在每个字符串中查找，而不是具体情况。

我认为下面的链接上的代码是接近的，但这并不是指一个范围。另外，我只需要搜索一个名为FAIR的工作表。
VBA遍历表：删除行if单元格不包含

我是VBA的完整新手，所以任何帮助都非常感谢。

解决方案

这是一种非VBA的方法。选择要更改的范围，转到条件格式化>突出显示单元格规则>更多规则>使用公式来确定要格式化的单元格。选择一种颜色以突出显示单元格，并使用范围键入此公式：

= COUNTIF（FAIR！$ A $ 1：$ A $ 10，A1 ）其中FAIR！$ A $ 1：$ A $ 10是您的关键字范围，A1是您尝试更改的范围的第一个单元格。

然后，您可以通过color = no fill来过滤列表，只能选择并删除可见单元格（Ctrl + G>特殊>可见单元格）。

I have a sheet of data with 25k lines. I need to search the entire sheet for certain words that I've defined in a named range on tab 2, called "KeywordSearh". The range contains a list of words I need to look up in the main data. I want to delete all rows that DO NOT contain these key words (and move all retaining rows up) and retain only the rows with reference to the keywords (including the titles row). Keywords could be written as text inside any cell which will also contain other text, so the search function needs to look within each string and not be case specific.

I think the code on link below is close, but this is does not refer to a range. Also, I only need to search one worksheet called "FAIR". VBA Looping Over Sheets: Delete rows if cell doesn't contain

I'm a complete novice to VBA so any assistance is extremely appreciated.

解决方案

Here is a non VBA way to do it. Select the range you want to alter, go to conditional formatting > highlight cell rules > more rules > use formula to determine which cells to format. Select a color to highlight the cells and type this formula with your ranges:

=COUNTIF(FAIR!$A$1:$A$10,A1) where FAIR!$A$1:$A$10 is your keyword range and A1 is the first cell of the range you are trying to alter.

You can then filter your list by color = no fill, select and delete only visible cells (Ctrl+G > Special > Visible Cells Only).

这篇关于vba删除不包含在范围中定义的设置值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！