Returning contents of last cell in row

Rebecca - Oct 1, 2008 at 01:52 PM
 penno - Oct 19, 2010 at 08:57 AM

I am trying to write a macro that returns the data contents of the last cell in a row of data. I then need to declare the cell's data as a variable.

So far, my code is this (from another online tutorial):

Public Sub LastColumnInOneRow()
'Find the last used column in Row1: row 1 in this example
Dim LastCol1 As Double
With ActiveSheet
LastCol1 = Range("IV3").End(xlToLeft).Column
End With
MsgBox LastCol1
End Sub

I only seem to be about to get hte program to return a count of the cols for me, not the actual cell data. Please help.



4 responses

Do you know how I would get the value pasted into a new cell using this macro
Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110
Oct 1, 2008 at 04:01 PM
Hello Rebecca,
to make the program return the data in the cell, use .Value
e.g. :
Dim LastCol1 as Double
Dim MyVariable

LastCol1 = Range("IV3").End(xlToLeft).Column 
MyVariable = Cells(3,LastCol1).Value
MsgBox MyVariable

ou can also write
MyVariable = Range("IV3").End(xlToLeft).Value

I would like to take the last amount in a column and subtract it from a number and have the value displayed in a cell. What forumla can I use or macro
im new to this but i would suggest you use the variable to find the last cell. say you called this variable

Dim lastcellcount

you could then use the msg function and the .show

for example

so it would look like this

Dim lastcellcount
lastcellcount = (maybe use your previous example)

hi ive looked at it a bit and come up with this

Dim numberofentries
numberOfTimeSheets = ActiveWorkbook.Worksheets.Count
If numberOfTimeSheets > 0 Then
Set myWorksheet = ActiveWorkbook.Worksheets.Item(numberofentries)
MsgBox "The contents of the last cell is " &_
myWorksheet.((name of the column containing the data)) & "."
End If
If numberOfTimeSheets = 0 Then
MsgBox "There is no contents."
End If