Скачать или смотреть How to Delete All Rows Except Those with a Specific Value in Excel Using VBA

Learn how to efficiently keep certain rows in Excel and delete the rest using VBA with this detailed guide.
---
This video is based on the question https://stackoverflow.com/q/74136018/ asked by the user 'Stefano Piacente' ( https://stackoverflow.com/u/19076195/ ) and on the answer https://stackoverflow.com/a/74136073/ provided by the user 'Pᴇʜ' ( https://stackoverflow.com/u/3219613/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Delete all rows except the ones with a specific value

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Deleting Rows in Excel Based on Column Values

When working with data in Excel, there may come a time when you need to clean up your spreadsheet by retaining only certain rows while deleting the others. This can be particularly daunting if you have large datasets. What if I told you that with a simple VBA script, you can easily achieve this? In this guide, we will explore how to retain only the rows containing a specific value—essentially, how to delete all other rows.

The Problem

Imagine you have a long list of data in an Excel sheet, and you need to keep only the rows that contain the value ITT1 in a specific column. The typical approach might involve manually reviewing each row and deleting the unnecessary ones. However, this can be time-consuming and error-prone, especially with larger datasets. Instead, we will leverage Visual Basic for Applications (VBA) to automate this task.

The Solution

Step 1: Setting up Your VBA Environment

Before you can execute any VBA automation, you need to access the Visual Basic for Applications editor in Excel:

Open your Excel file.

Press ALT + F11 to open the VBA editor.

Right-click on VBAProject (YourWorkbookName), navigate to Insert, and select Module to create a new module.

Step 2: The Basic Loop Structure

Here’s a straightforward way to delete rows that do not contain the value ITT1:

[[See Video to Reveal this Text or Code Snippet]]

In the above code, we loop through the rows in reverse (from the bottom to the top) to avoid skipping any rows after deletions. However, there is a more efficient way to write this.

Step 3: Negating the Condition

To delete all rows except the ones that meet the condition, you need to negate your If statement:

[[See Video to Reveal this Text or Code Snippet]]

Using this approach, you will delete any row that does not match your specific value. Let’s integrate this into our structured code:

[[See Video to Reveal this Text or Code Snippet]]

Step 4: Improving Efficiency

If you have a large number of rows to delete, the above approach may become slow. To enhance performance, you can collect the rows to delete in a range variable and then delete them all at once at the end:

[[See Video to Reveal this Text or Code Snippet]]

Step 5: Final Touches

Now, instead of running in reverse order, you can simply run the loop from top to bottom:

[[See Video to Reveal this Text or Code Snippet]]

This change can simplify the code, especially when using the collection method.

Conclusion

Using VBA offers a powerful method to manage and manipulate data in Excel efficiently. By implementing the techniques described above, you can seamlessly delete rows that do not meet your criteria while keeping your desired data intact. Not only does this save you time, but it also minimizes the potential for human error in data management.

As you grow more comfortable with VBA, you'll find various ways to further enhance your Excel tasks. Happy coding!