last cell without a blank
 

I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
..
..
32

In this example it would pick 5/07

last cell without a blank
 

Try,

Range("IV2").End(xlToLeft).Offset(-1, 0).Select

Mike

"Scott" wrote:

I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
.
.
32

In this example it would pick 5/07

last cell without a blank
 

That didnt seem to work. What is the "IV2" for?

"Mike H" wrote:

Try,

Range("IV2").End(xlToLeft).Offset(-1, 0).Select

Mike

"Scott" wrote:

I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
.
.
32

In this example it would pick 5/07

last cell without a blank
 

Scot,

It starts looking in cell IV2 and moves left (xltoleft) until it finds a
populated cell and selects the cell above that.

Mike

"Scott" wrote:

That didnt seem to work. What is the "IV2" for?

"Mike H" wrote:

Try,

Range("IV2").End(xlToLeft).Offset(-1, 0).Select

Mike

"Scott" wrote:

I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
.
.
32

In this example it would pick 5/07

last cell without a blank
 

I need to look in cells a2:z32 which is more than one row.

"Mike H" wrote:

Scot,

It starts looking in cell IV2 and moves left (xltoleft) until it finds a
populated cell and selects the cell above that.

Mike

"Scott" wrote:

That didnt seem to work. What is the "IV2" for?

"Mike H" wrote:

Try,

Range("IV2").End(xlToLeft).Offset(-1, 0).Select

Mike

"Scott" wrote:

I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
.
.
32

In this example it would pick 5/07

last cell without a blank
 

not sure what you're looking for, but see if this helps: it will add the last
blank cell's address for rows 2 to 32 to the array, so your example would look
like this if column F was blank

$F$2
$E$3
$F$4
$F$5


Sub Real_lastrow()
Dim lastcol As Long
Dim ws As Worksheet
Dim arr As Variant
Dim addr As String
Dim i As Long
Set ws = Worksheets("Sheet1")
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)

For i = 2 To 32
addr = ws.Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Address
If IsEmpty(Range(addr)) Then
On Error Resume Next
arr(i) = addr
Debug.Print arr(i)
On Error GoTo 0
End If
Next
End Sub

--


Gary


"Scott" wrote in message
...
I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
.
.
32

In this example it would pick 5/07

last cell without a blank
 

Thanks Gary. I was hoping something like this would do but is only seems to
work on row 2 of the group of cells

LastDate = Range("Z2:Z5").End(xlToLeft).Offset(-1, 0)

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

"Gary Keramidas" wrote:

not sure what you're looking for, but see if this helps: it will add the last
blank cell's address for rows 2 to 32 to the array, so your example would look
like this if column F was blank

$F$2
$E$3
$F$4
$F$5


Sub Real_lastrow()
Dim lastcol As Long
Dim ws As Worksheet
Dim arr As Variant
Dim addr As String
Dim i As Long
Set ws = Worksheets("Sheet1")
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
ReDim arr(1 To lastcol)

For i = 2 To 32
addr = ws.Cells(i, Columns.Count).End(xlToLeft).Offset(, 1).Address
If IsEmpty(Range(addr)) Then
On Error Resume Next
arr(i) = addr
Debug.Print arr(i)
On Error GoTo 0
End If
Next
End Sub

--


Gary


"Scott" wrote in message
...
I have a series of dates in cells a1:z1 and some numeric data in some of the
cells a2:z32. I would like to find the last column that contains a value in
any of the data cells (all columns after that contain a blank) and then
select the date cell that corresponds to it. Can anyone help? Thanks.

a b c d e ... z
1 1/07 2/07 3/07 4/07 5/07
2 5 0 7 2
3 0 0
4 0 1 6 98 12
5 4 3 0 0 1
.
.
32

In this example it would pick 5/07