Скачать или смотреть How to Add Borders Around UsedRange in Excel VBA, Excluding Header Rows

Learn how to effectively add borders around a used range in Excel VBA while excluding the header rows for a cleaner presentation in your spreadsheets.
---
This video is based on the question https://stackoverflow.com/q/72622293/ asked by the user 'NotACoderNoah' ( https://stackoverflow.com/u/18172482/ ) and on the answer https://stackoverflow.com/a/72622404/ provided by the user 'FaneDuru' ( https://stackoverflow.com/u/2233308/ ) 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: VBA-Add Border around UsedRange minus Header rows (first 6 rows)

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.
---
How to Add Borders Around UsedRange in Excel VBA, Excluding Header Rows

When working with Excel, it’s common to deal with datasets that include header rows. If you're looking to enhance the appearance of your data by adding borders around specific ranges, you might run into a few hurdles along the way—especially when trying to exclude header rows.

In this guide, we'll address a commonly encountered problem: how to add a border around the used range of an Excel worksheet, minus the header rows. Let’s walk through the steps to achieve this seamlessly using VBA.

Understanding the Problem

You may have encountered a situation where you want to add borders around a specific range of data, but you need to exclude the header rows at the top. For instance, you want to highlight rows after the initial six rows, which typically contain the headers.

A common error that arises when executing a VBA script for this task is the "Run-time error 424: Object Required." This usually hints at issues with how you're referencing ranges or how you're trying to apply borders.

The Solution

Instead of simply selecting the range, you can directly manipulate it to ensure that the borders are applied correctly. Below are the steps you need to take to achieve this.

Step-by-Step Code Explanation

Declare the Range Variables:
To start, you’ll need to declare your variable for the range that you want to work with.

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

Set the Used Range:
Next, set your range variable to the used range of the specific worksheet you’re working on. For our example, we’ll reference the worksheet named "con-1".

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

Adjust the Range to Exclude Header Rows:
To focus on the data below the header rows, use the Resize and Offset methods. This allows you to adjust the range by excluding the first six rows.

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

Consolidated Code

Here’s how the entire code snippet will look after applying the modifications:

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

Explanation of Key Methods:

Resize: This method changes the size of a range (in this case, it lowers the count of rows by 6 to exclude the headers).

Offset: This moves your starting point down by 6 rows, again focusing on the data following the headers.

BorderAround: This applies the border around the final specified range.

Conclusion

By following this guide, you should now be able to add borders around your used range in an Excel worksheet efficiently while omitting the header rows. Implementing these small changes in your VBA code can significantly enhance the presentation of your datasets and reduce errors in your script.

If you have any additional questions or run into further issues, feel free to reach out or leave a comment! Happy coding!