How do i find the last entry in a column where every cell is not .

[Guest]

I have an excel worksheet that keeps track of our gas inventory. We get gas
delivered once a week and when its delivered we post the price. I wind up
with a column that has prices and non entries. Month End i need the LAST
price we were charged to produce a report. It would be nice if Visual Basic
had a function called Last but alas I have not found it. I have to do a lot
of gyrations to get this data. Does someone know an easy way to get this data.

 

[Norman Jones]

Hi Beau Black,

Try:

Sub Tester()
Dim LastPrice As Double
Const Col As String = "A" '<<===== CHANGE
LastPrice = Cells(Rows.Count, Col).End(xlUp).Value
MsgBox LastPrice '<<==== For Test
purposes only
End Sub


Change the value of Col to accord with your column.

 

[Frank Kabel]

Hi
see:
http://www.xldynamic.com/source/xld.LastValue.html

--
Regards
Frank Kabel
Frankfurt, Germany

I have an excel worksheet that keeps track of our gas inventory. We get gas
delivered once a week and when its delivered we post the price. I wind up
with a column that has prices and non entries. Month End i need the LAST
price we were charged to produce a report. It would be nice if Visual Basic
had a function called Last but alas I have not found it. I have to do a lot
of gyrations to get this data. Does someone know an easy way to get

this data.

 

[Harald Staff]

Hi

Adding: If you by Last mean "last entered, but not necessarily downmost"
then you need a date-time-stamp or similar in the neighbor column.

HTH. Best wishes Harald

I have an excel worksheet that keeps track of our gas inventory. We get gas
delivered once a week and when its delivered we post the price. I wind up
with a column that has prices and non entries. Month End i need the LAST
price we were charged to produce a report. It would be nice if Visual Basic
had a function called Last but alas I have not found it. I have to do a lot
of gyrations to get this data. Does someone know an easy way to get this

data.

 

[Sharad]

Well the easiest way out:

1. In the worksheet, chose a conveninet cell where you want to display
the last price enter. Name this cell as "LastPrice".

2. In that worksheet_change event procedure add following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
Range("LastPrice").Value = Target.Value
End If
End Sub

Above is assuming price is in coloumn 6 (F). Change the coloumn number
accordingly.

Sharad