Returning contents of last cell in rowClosed

Question

Hello,

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.

Thanks,

R

Sharkiness · Accepted Answer

Do you know how I would get the value pasted into a new cell using this macro

Ivan-hoe · Answer

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
Ivan

penno · Answer

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 

msgbox lastcellcount.show
so it would look like this 

Dim lastcellcount
lastcellcount = (maybe use your previous example)

msgbox lastcellcount.show

penno · Answer

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

Returning contents of last cell in row

4 responses

Similar discussions

Subscribe To Our Newsletter!