Accessing an Excel cell

Topic

New Reply

Does anyone know how to access, the last value, in an Excel cell

Reply | Quote

Replies

glukhurst

Typically the Validation list is known to be a range, or maybe some array in VBA.

If by last value you mean value # 5 in a 5-value list, then you can do:

1) If the list is in a range say in “E1:E5” use this:
lLastValue = Range(“E1:E5”).Cells(Range(“E1:E5”).Cells.Count).Value

2) If the list is in an array say called ListOfValidEntries then use this:
lLastValue = ListOfValidEntries(UBound(ListOfValidEntries))

I hope this helps.

Wassim

Reply | Quote

Many thanks

This has put me on the right track.

Reply | Quote

Gluckhurst

It looks to be especially long-winded

Range(“A1”).Validation returns the Validation object

If you knew you were dealing with a list, I think you could just use

.Validation.Formula1 which holds the array as a comma delimited list, or as a rangename – I’ve never tried decoding Formula1, although I have set it.

Reply | Quote

As the formula1 property of the validation method returns the range address, you can use the code below to find the last value in a validation list:

Assume here that you have data to enter in the range B2:B10 and that a validation list is used for this. Then Range(“B2:B10”).Validation.Formula1 returns the range address of the validation list.

Sub test()
  Dim n As Integer
  Dim ValAddress As String
  ValAddress = Range("B2:B10").Validation.Formula1
  n = Range(ValAddress).Cells.Count
  MsgBox Range(ValAddress).Cells(n).Value
End Sub

Hope this helps.

Reply | Quote