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

 

[Guest]

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.

 

[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 .. an
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 Sheet
in the spreadsheet. Do the same for Sheet2 and Sheet3. The Access d
will then have 3 'tables' shown in the tables area.

Go to Queries and design a query in using the SQL View (other option
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 thre
tables. Save query as (say) MyData.

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

I just tried it and it worked fine but it seems to insist that th
final pivot table is in a spreadsheet other than the spreadshee
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

 

[Guest]

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]

You legend, Andy...~!!

 

[Guest]

Thanks for the feedback!

Andy.