Скачать или смотреть How to Get the Last Column Number in Excel with Hidden Columns

Discover how to retrieve the last column number in Excel even if some columns are hidden. Learn step-by-step with practical VBA solutions!
---
This video is based on the question https://stackoverflow.com/q/75398341/ asked by the user 'Waleed' ( https://stackoverflow.com/u/17797849/ ) and on the answer https://stackoverflow.com/a/75399702/ 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: Get the last column number while the column is hidden

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 Get the Last Column Number in Excel with Hidden Columns

Have you ever faced the challenge of finding the last column number in an Excel sheet, only to realize that some columns might be hidden? This can lead to confusion, especially when using straightforward methods that do not account for these hidden columns. In this guide, we will walk you through a simple yet effective solution using VBA (Visual Basic for Applications) that allows you to accurately find the last column number, even when some columns are not visible. Let's dive in!

The Problem: Finding the Last Column Number

When you attempt to locate the last column that contains data in a row, the usual approach is to count from the right using the following VBA code:

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

While this code works, it falls short if the last column is hidden. In such cases, the result returned might not accurately reflect the actual last column with data. Fortunately, there's a better way to tackle this problem.

The Solution: Using the Find Method

The Find method in VBA is a powerful tool that can help avoid the complications posed by hidden cells. It enables you to search through a range without being affected by the visibility of columns. Below is a refined approach to find the last column number in the second row, even if some columns are hidden:

Step-By-Step Implementation

Open the Visual Basic for Applications (VBA) Editor:

Press ALT + F11 in Excel to open the VBA editor.

Insert your VBA Code:

Create a new module and paste the following code:

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

Code Explanation

Setting the Worksheet and Range: The variable ws is assigned to the active worksheet. rng2 references the second row of the used range in the worksheet.

Using the Find Method: The Find function looks for any value (represented by "*"), starting from the first cell in the row. It searches through all cells, disregarding their visibility (hidden or shown).

Output: Finally, the last column number found is printed in the Immediate Window using Debug.Print.

Why This Method Works

Avoids Issues with Hidden Columns: By using Find, you effectively bypass issues caused by any hidden columns when searching for the last column with data.

Reliable Range Selection: The UsedRange ensures you are working with the actual data present in the sheet, and targeting a specific row allows for precise results.

Conclusion

Finding the last column number in an Excel sheet, even when columns are hidden, is made easy with this effective approach using VBA. By leveraging the Find method, you can ensure that your results remain accurate no matter the visibility of the columns. Implement this code in your Excel projects to simplify your data analysis tasks!

Feel free to share your thoughts or any questions you may have regarding this topic in the comments below! Happy coding!