software

I am dealing with about 20 different worksheets of real estate data in
one Excel file and am in the process of putting them all into 1
worksheet - because the total number of rows is just less than 65,536!

Before I copy one of the 20 worksheets into the main worksheet I need
to filter the data using EasyFilter (by Ron de Bruin MVP) and I need to
highlight a whole column of data from the top populated cell to the
bottom populated cell before I can filter it. Two (2) questions please:

1. How can I do this highlighting process quickly without have to wait
a long time before the cursor reaches the bottom row (usually about
3000 rows long)? By the way, I cannot simply click the column header at
the top because the column needs to start at a specific populated cell
and finish at the end of the column of data - not just the whole column
as if I was deleting it.

2. Is there a simple way for me to accumlate all the worksheets into
one main worksheet without have to copy and paste each one into the
main worksheet?

Any tips that people might have about working with long lists would be
great - as well as creating Pivot Tables across more than one
worksheet.

Thanks!

Regards
Pelham HigginsHi

Select the first cell and hold shift while you double-click on the bottom
border of the cell. As long as there are no gaps, this will highlight down
to the bottom cell in the column.

Andy.

quot;Pelhamquot; gt; wrote in message oups.com...
gt;I am dealing with about 20 different worksheets of real estate data in
gt; one Excel file and am in the process of putting them all into 1
gt; worksheet - because the total number of rows is just less than 65,536!
gt;
gt; Before I copy one of the 20 worksheets into the main worksheet I need
gt; to filter the data using EasyFilter (by Ron de Bruin MVP) and I need to
gt; highlight a whole column of data from the top populated cell to the
gt; bottom populated cell before I can filter it. Two (2) questions please:
gt;
gt; 1. How can I do this highlighting process quickly without have to wait
gt; a long time before the cursor reaches the bottom row (usually about
gt; 3000 rows long)? By the way, I cannot simply click the column header at
gt; the top because the column needs to start at a specific populated cell
gt; and finish at the end of the column of data - not just the whole column
gt; as if I was deleting it.
gt;
gt; 2. Is there a simple way for me to accumlate all the worksheets into
gt; one main worksheet without have to copy and paste each one into the
gt; main worksheet?
gt;
gt; Any tips that people might have about working with long lists would be
gt; great - as well as creating Pivot Tables across more than one
gt; worksheet.
gt;
gt; Thanks!
gt;
gt; Regards
gt; Pelham Higgins
gt;

quot;2. Is there a simple way for me to accumlate all the worksheets into
one main worksheet without have to copy and paste each one into the
main worksheet?quot;

You can do this quite easily using a database .. eg MS Access .. and
without hitting the 64K row limit.

Assume a spreadsheet with worksheets Sheet1, Sheet2, Sheet3
Each has columns Name, Value, etc .. all have same column headings.

In Access, use 'get external data' -gt; 'Link Table' and link to Sheet1
in the spreadsheet. Do the same for Sheet2 and Sheet3. The Access db
will then have 3 'tables' shown in the tables area.

Go to Queries and design a query in using the SQL View (other options
are Design View and Datasheet view .. in the top left hand corner).
Type the query as:
Select * from Sheet1; union all
select * from Sheet2; union all
select * from Sheet3

Execute the query and the result set should be every row in the three
tables. Save query as (say) MyData.

In a new spreadsheet create a pivot table using external data connected
to the MyData query in the Access db.

I just tried it and it worked fine but it seems to insist that the
final pivot table is in a spreadsheet other than the spreadsheet
continining the data .. you should test this.

I dont think it gets much simpler ;-)

regards.--
steven1001
------------------------------------------------------------------------
steven1001's Profile: www.excelforum.com/member.php...oamp;userid=30757
View this thread: www.excelforum.com/showthread...hreadid=527461Steven1001

Many thanks - the solution is far more complicated than I thought so I
might do my best to use only one worksheet unless I know for certain
that I have more than 65,000 lines of data...!

Regards
PelhamAndy

Brilliant - thanks, you taught me something very convenient with a long
list!

However, what happens if I have got gaps because it is the gaps that I
am trying to filter out by highlighting the whole column in the first
place???

PelhamIn that case, click on your first cell and type the remainder of the range
into the Name box. For example, if you want to select A2:A35000, click in A2
and then click into the Name box (just above column A) and add :A35000 to
the box - giving you A2:A35000

Hope this helps.
Andy.

quot;Pelhamquot; gt; wrote in message oups.com...
gt; Andy
gt;
gt; Brilliant - thanks, you taught me something very convenient with a long
gt; list!
gt;
gt; However, what happens if I have got gaps because it is the gaps that I
gt; am trying to filter out by highlighting the whole column in the first
gt; place???
gt;
gt; Pelham
gt;
You legend, Andy...~!!Thanks for the feedback!

Andy.

quot;Pelhamquot; gt; wrote in message oups.com...
gt; You legend, Andy...~!!
gt;