Solved

Copy rows into a new excel document, where column has a value in.

Posted on 2014-11-04
3
157 Views
Last Modified: 2014-11-04
Hi

I have a document that contains lots of rows, I want to automatically copy all the rows that contain a number in column G, into a new excel spreadsheet.

So I am thinking a macro would work best for this?

Thanks,

Andy
5digitareacodestest.xls
0
Comment
Question by:andygen
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40422626
A macro could do this, but you can easily do this manually with these steps:

1) Make a copy of the existing sheet, either by:
    a) [Ctrl]+click-n-drag the sheet tab to the right until you see a black triangle then release.  A duplicate sheet is created.
    b) Right-click on the sheet tab, select "Move or Copy..." from the shortcut menu.  Click the "Create a copy" check box and click the "OK" button.
2) Rename the new sheet as you wish
3) Turn on Data Filters
    a) Select cell A1
    b) Press [Shift]+[Ctrl]+[End] to select all the cells in the range
    c) Menu:  Data - Filter
4) Click the drop filter in column G.  Click the "(Select All)" box to turn off all filters, then scroll down and click the "(Blanks)" check box.  Click the "OK" button.
5) Delete these rows
    a) Select cell A2
    b) Press [Shift]+[Ctrl]+[End] to select all the cells in the range
    c) Right-click anywhere in the selected area and select "Delete Row" from the shortcut menu.  You'll be prompted again; click the "OK" button.
6) Turn off the Filters.

You now have a sheet containing only rows that have a value in column G.

The example file you provided had 9,435 rows with blank values out of 62,085 rows of data.  You can check your steps against this to ensure you have done it correctly.

Regards,
-Glenn
0
 

Author Comment

by:andygen
ID: 40422714
Hi Glenn

Worked great thanks!

Andy
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40422830
You're welcome.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

867 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

20 Experts available now in Live!

Get 1:1 Help Now