Re: [ExcelVBA] Select range in column with non-continuous data

 

Try:

NNNN = ActiveCell.SpecialCells(xlLastCell).Row

or:
NNNN = application.worksheetfunction.counta(Range("A:A")) 

Then:
Range("B1:B" & NNNN).Select

Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: 1z <1z@compuserve.com>
To: ExcelVBA@yahoogroups.com
Sent: Wed, June 27, 2012 8:32:56 AM
Subject: Re: [ExcelVBA] Select range in column with non-continuous data

 

hmmmm....

What about going backwards.... do an f5/go to/ or type an address in the
"address" box... to a cell you are sure is past the last row. If you can name
that cell even better... then do an End Up. Now do something like shift ctrl
home. If you just want col B then you can just move the selection over a column.

Or... if the columns are the same "length"...

End down in col A and move over a col to B and then shft ctrl home and move back
a col.

Just musing... not tried it out.

HTH
Let us know how you get on...
Lisa

Sent: Wed, Jun 27, 2012 11:32 am
Subject: [ExcelVBA] Select range in column with non-continuous data

This is not a VBA-question.

hope I can explain it and get some good advice anyway.

n a worksheet - 3 columns, about 10000 rows of data.
n column A and C the data are continuous - all cells are filled with data
n column B - some cells are empty.

want to select the range B1.BNNNN (where NNNN is the last row with data), but
t's not possible to use END-DOWN because of the empty cells in that column.

s there any way around?

hanks in advance!

orstein

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (6)

Recent Activity:

• New Members 4

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___