last cell without a blank

S

Scott

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
 
M

Mike H

Scot,

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

Mike
 
G

Gary Keramidas

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
 
S

Scott

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)

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top