VBA to get last value in range and return column...

[RobbieC]

Hi there, this has been driving me crazy...

I have a range (EF60:IV60) and I need to return the last value which is not blank or 0. I'm also looking to return the column that this value is sitting in...

(I've tried this with conventional formulas, but it is not reliable for some reason)

If you can point me in the right direction, I'd be most grateful

Thanks

 

[Joe4]

Would there possibily be anything in row 60 AFTER column IV, or not?

 

[RobbieC]

Yes, there is data all over the shop

 

[Joe4]

Try this:

Sub MyMacro()

    Dim lastCell As Range

    If Range("IV60") <> "" Then
        Set lastCell = Range("IV60")
    Else
        Set lastCell = Range("IX60").End(xlToLeft)
    End If
        
    If lastCell.Column >= 136 Then
        MsgBox "Last value is in cell " & lastCell.Address(0, 0) & " and is " & lastCell
    Else
        MsgBox "There are no values in the range EF60:IV60"
    End If

End Sub

 

[RobbieC]

Brilliant, thanks for that... I'll give it a whirl

Cheers

 

[Joe4]

You are welcome.

 

[Rick Rothstein]

Here is another macro that you can try...

[table="width: 500"]
[tr]
	[td]Sub LastNonZeroNoBlankCell()
  Dim lastCell As Range
  Set lastCell = Cells(60, 135 + Len(RTrim(Join(Evaluate("IF(EF60:IV60<>0,""X"","" "")"), ""))))
  MsgBox "Last cell's value: " & lastCell.Value
  MsgBox "Last cell's address: " & lastCell.Address
End Sub[/td]
[/tr]
[/table]

 

[Joe4]

Here is another macro that you can try...

It produces some odd results if no cells are populated in that range.

 

[Rick Rothstein]

It produces some odd results if no cells are populated in that range.

I forgot to include a check for that. Here is my code revised to handle this situation should it arise (plus I made a couple of minor changes, one because the range is fixed and non-changing)...

[table="width: 500"]
[tr]
	[td]Sub LastNonZeroNoBlankCell()
  Dim LastCell As Range
  Set LastCell = Cells(60, 135 + Len(RTrim(Join([IF(EF60:IV60<>0,"X"," ")], ""))))
  If LastCell.Address(0, 0) = "EE60" Then
    MsgBox "There are no non-zero values in the range EF60:IV60"
  Else
    MsgBox "Last cell's value: " & LastCell.Value
    MsgBox "Last cell's address: " & LastCell.Address(0, 0)
  End If
End Sub[/td]
[/tr]
[/table]

 

[RobbieC]

Brilliant! Thanks guys!