Solved

Select all data in a column that has blank cells

Posted on 2014-02-22
2
312 Views
Last Modified: 2014-02-22
Hi,

I have a macro that copies a column from one sheet to another.

Column A has no blanks but column B does.

When I use the following to copy, it only selects data down to the first blank cell instead of the entire column.

    Sheets("test1").Select
    Range("B1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("test2").Select
    Range("B1").Select
    ActiveSheet.Paste

I'm guessing the best way is to set a "range" which would be the number of rows with data in column A, then use that range value to copy columns that have blank cells but haven't found a good useable example googling.

Thanks in advance,

swjtx99
0
Comment
Question by:swjtx99
2 Comments
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
Comment Utility
The simple change to select the entire common is
Range("B:B") and take out Range(Selection, Selection.End(xlDown)).Select all together

do you need a more targeted approach?
0
 

Author Closing Comment

by:swjtx99
Comment Utility
Hi Regmigrant,

Thanks. Looks like that works!

Regards,

swjtx99
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifiā€¦
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now