selecting column down to variant cell
 

Is it possible to find the bottom of a sheet, say there are 1500 entries in
column A... and then select F9:F1500 if column F doesn't have values in every
row?

For example, the next time the macro is run thier maybe 1505 rows in the
sheet...

here is a test code that doesn't work for me... but may help convey my idea.

Dim c As Variant

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, Selection.c(xlDown)).Select

selecting column down to variant cell
 

Try this John

Sub test()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Range("F9:F" & lr).Select
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
Is it possible to find the bottom of a sheet, say there are 1500 entries in
column A... and then select F9:F1500 if column F doesn't have values in every
row?

For example, the next time the macro is run thier maybe 1505 rows in the
sheet...

here is a test code that doesn't work for me... but may help convey my idea.

Dim c As Variant

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, Selection.c(xlDown)).Select

selecting column down to variant cell
 

John,

This is how your code should have been written to work:

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, c)).Select

But this one line will work just as well

Range("A9", Range("A9").End(xlDown)).Offset(0, 5).Select

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Is it possible to find the bottom of a sheet, say there are 1500 entries in
column A... and then select F9:F1500 if column F doesn't have values in every
row?

For example, the next time the macro is run thier maybe 1505 rows in the
sheet...

here is a test code that doesn't work for me... but may help convey my idea.

Dim c As Variant

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, Selection.c(xlDown)).Select

selecting column down to variant cell
 

Bernie,

The OP should go with Ron's code which works from the bottom of the page -
up

Your code works from the top down and will stop at the 1st blank cell. The
OP says that there are sporadic blank cells...

--
steveB

Remove "AYN" from email to respond
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
John,

This is how your code should have been written to work:

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, c)).Select

But this one line will work just as well

Range("A9", Range("A9").End(xlDown)).Offset(0, 5).Select

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Is it possible to find the bottom of a sheet, say there are 1500 entries
in
column A... and then select F9:F1500 if column F doesn't have values in
every
row?

For example, the next time the macro is run thier maybe 1505 rows in the
sheet...

here is a test code that doesn't work for me... but may help convey my
idea.

Dim c As Variant

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, Selection.c(xlDown)).Select

selecting column down to variant cell
 

That's why I used column A, since the sporadic blanks cells are in column F.

HTH,
Bernie
MS Excel MVP

Your code works from the top down and will stop at the 1st blank cell. The OP says that there are
sporadic blank cells...

Is it possible to find the bottom of a sheet, say there are 1500 entries in
column A... and then select F9:F1500 if column F doesn't have values in every
row?

selecting column down to variant cell
 

Thank yous to all of you, both Ron's and Bernie's one liner worked...

my code adaption didn't, but problem solved.

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, c)).Select

Thanks again!


"Ron de Bruin" wrote:

Try this John

Sub test()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Range("F9:F" & lr).Select
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"John" wrote in message ...
Is it possible to find the bottom of a sheet, say there are 1500 entries in
column A... and then select F9:F1500 if column F doesn't have values in every
row?

For example, the next time the macro is run thier maybe 1505 rows in the
sheet...

here is a test code that doesn't work for me... but may help convey my idea.

Dim c As Variant

Range("A9").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Select
Set c = ActiveCell
Range("f9").Select
Range(Selection, Selection.c(xlDown)).Select

selecting column down to variant cell
 

see my second post above!

"Bernie Deitrick" wrote:

That's why I used column A, since the sporadic blanks cells are in column F.

HTH,
Bernie
MS Excel MVP

Your code works from the top down and will stop at the 1st blank cell. The OP says that there are
sporadic blank cells...

Is it possible to find the bottom of a sheet, say there are 1500 entries in
column A... and then select F9:F1500 if column F doesn't have values in every
row?

selecting column down to variant cell
 

John,

My mistake - I left in an extra ) on that last line: take it out, and that code will work.

HTH,
Bernie
MS Excel MVP

my code adaption didn't, but problem solved.