Скачать или смотреть How to Efficiently Copy Data from an External Worksheet Including Hidden Rows in Excel VBA

Learn how to copy data from an external Excel worksheet while including hidden rows using VBA. This guide provides clear steps and sample code solutions.
---
This video is based on the question https://stackoverflow.com/q/64585211/ asked by the user 'BoyDespasito' ( https://stackoverflow.com/u/14540120/ ) and on the answer https://stackoverflow.com/a/64585598/ provided by the user 'Dy.Lee' ( https://stackoverflow.com/u/8107092/ ) 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: Copying a set of data from External worksheet including Hidden 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.
---
Introduction

If you’ve ever tried to copy data from an external Excel worksheet only to discover that some rows were hidden due to filters, you’re not alone. Many users face this challenge, especially when working with extensive datasets that contain vital information. The good news is that it’s possible to copy all the data—including hidden rows—using a simple yet effective VBA (Visual Basic for Applications) script in Excel.

In this guide, we'll walk you through the solution step-by-step. Whether you’re a seasoned coder or just getting started with VBA, this guide is designed to help you successfully copy data from a filtered external worksheet without losing any hidden information.

Understanding the Problem

When copying data from Excel worksheets that have filters applied:

Some rows may be hidden from view.

Standard copy commands will only transfer visible data.

We need a method to reveal and copy all data, including the hidden rows.

To accomplish this, we can use VBA to automate the process by utilizing Excel’s functionality to clear filters before copying the data.

The Solution

To copy data from an external worksheet including hidden rows, we’ll make use of the ShowAllData function in our VBA script. Below, I will outline the necessary steps along with complete code for you to use.

Step-by-Step Breakdown

Set Up Your Environment

Open your Excel workbook where you want to copy data. This will be referred to as the destination workbook.

Ensure that the external workbook you’ll be copying data from (the source) is accessible.

Open the External Workbook

Use the Application.GetOpenFilename method to prompt the user to select the source workbook.

Check For Active Filters

Before copying the data, check if any filters are applied using Worksheet.FilterMode.

Remove Filters

If filters are active, execute the ShowAllData method to display all data, including the previously hidden rows.

Copy Data

Use a two-dimensional array to copy the entire dataset to avoid missing any information.

Close the Source Workbook

After copying, close the source workbook without saving changes.

Example Code

Here’s the complete VBA code that incorporates the above steps:

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

Conclusion

In just a few steps and with the help of some VBA coding, you can successfully copy all data from an external worksheet, ensuring that no hidden rows are left behind. This method saves you time and streamlines your data management process.

Whether you're a beginner or someone more experienced with Excel, mastering these simple techniques can significantly elevate your efficiency when handling data. Happy coding!