Highlighting a whole column

[Pelham]

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 Higgins

Hi

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.

"Pelham" <pelham.higgins@cbre.co.jp> wrote in message
news:1143617119.369474.300710@z34g2000cwc.googlegroups.com...
>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 Higgins
>

[steven1001]

"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?"

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' -> '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.

[Pelham]

Steven1001

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
Pelham

[Pelham]

Andy

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???

Pelham

In 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.

"Pelham" <pelham.higgins@cbre.co.jp> wrote in message
news:1143706650.433181.147060@i39g2000cwa.googlegroups.com...
> Andy
>
> 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???
>
> Pelham
>

[Pelham]

You legend, Andy...~!!

Thanks for the feedback!

Andy.

"Pelham" <pelham.higgins@cbre.co.jp> wrote in message
news:1143710026.821011.246880@z34g2000cwc.googlegroups.com...
> You legend, Andy...~!!
>