Last Row…./ last cell
 

Hello All:
Searching the historical posts, I have found bunch of solutions that
will address the last row or maybe I should say locating the last row
in terms of holding data or not null. So with that I attempted this
code hoping that it will look and find the last cell in Colum A that
is not null and with that it assumes that the row is right after
that. So if A3 has “123W32M1” the code determines that A3 is the last
row and places the hello in A4. Of course I do realize that this
logic assumes that we always have data in Colum A.

Try #1
---------------------------------
Dim iLastRow As Integer
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = "Hello"
----------------------------------
I thought this would locate the last row and cell right after the one
that has data or not null from Colum A and place a hello in it. Now
mind you the hello was just a test to see where the code directing the
action at. It didn’t work so I tried this next one

-----------------------------
Sub LastRow1()
Dim LastRow As Long
With Worksheets("Location")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Hello"
End With
End Sub
-----------------------------
Still didn’t work. Now I am like totally confused. Any help is
appreciated.

Ardy

Last RowŠ./ last cell
 

You're finding the last row, but then inserting the value into the
ActiveCell.

You could use

With Worksheet("Location")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
End With
ActiveCell.FormulaR1C1 = "hello"

but why bother activating:

With Worksheets("Location")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "hello"
End With

Working with the range objects directly is faster, leads to smaller
code, and IMO is easier to maintain.

In article
,
Ardy wrote:

Hello All:
Searching the historical posts, I have found bunch of solutions that
will address the last row or maybe I should say locating the last row
in terms of holding data or not null. So with that I attempted this
code hoping that it will look and find the last cell in Colum A that
is not null and with that it assumes that the row is right after
that. So if A3 has ³123W32M1² the code determines that A3 is the last
row and places the hello in A4. Of course I do realize that this
logic assumes that we always have data in Colum A.

Try #1
---------------------------------
Dim iLastRow As Integer
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = "Hello"
----------------------------------
I thought this would locate the last row and cell right after the one
that has data or not null from Colum A and place a hello in it. Now
mind you the hello was just a test to see where the code directing the
action at. It didn¹t work so I tried this next one

-----------------------------
Sub LastRow1()
Dim LastRow As Long
With Worksheets("Location")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Hello"
End With
End Sub
-----------------------------
Still didn¹t work. Now I am like totally confused. Any help is
appreciated.

Ardy

Last Row€¦./ last cell
 

You are very close!

Sub ardy()
Dim iLastRow As Integer
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Cells(iLastRow + 1, "A").Value = "Hello"

End Sub

It dows not matter if you use .Value or .Formula; you must use iLastRow
after you have calculated it.
--
Gary''s Student - gsnu2007j


"Ardy" wrote:

Hello All:
Searching the historical posts, I have found bunch of solutions that
will address the last row or maybe I should say locating the last row
in terms of holding data or not null. So with that I attempted this
code hoping that it will look and find the last cell in Colum A that
is not null and with that it assumes that the row is right after
that. So if A3 has €œ123W32M1€� the code determines that A3 is the last
row and places the hello in A4. Of course I do realize that this
logic assumes that we always have data in Colum A.

Try #1
---------------------------------
Dim iLastRow As Integer
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = "Hello"
----------------------------------
I thought this would locate the last row and cell right after the one
that has data or not null from Colum A and place a hello in it. Now
mind you the hello was just a test to see where the code directing the
action at. It didnt work so I tried this next one

-----------------------------
Sub LastRow1()
Dim LastRow As Long
With Worksheets("Location")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Hello"
End With
End Sub
-----------------------------
Still didnt work. Now I am like totally confused. Any help is
appreciated.

Ardy

Last RowŠ./ last cell
 

On Jun 11, 6:58*am, JE McGimpsey wrote:
You're finding the last row, but then inserting the value into the
ActiveCell.

You could use

* *With Worksheet("Location")
* * * .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Activate
* *End With
* *ActiveCell.FormulaR1C1 = "hello"

but why bother activating:

* *With Worksheets("Location")
* * * .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = "hello"
* *End With

Working with the range objects directly is faster, leads to smaller
code, and IMO is easier to maintain.

In article
,



*Ardy wrote:
Hello All:
Searching the historical posts, I have found bunch of solutions that
will address the last row or maybe I should say locating the last row
in terms of holding data or not null. *So with that I attempted this
code hoping that it will look and find the last cell in Colum A that
is not null and with that it assumes that the row is right after
that. *So if A3 has ³123W32M1² the code determines that A3 is the last
row and places the hello in A4. *Of course I do realize that this
logic assumes that we always have data in Colum A.

Try #1
---------------------------------
Dim iLastRow As Integer
* * *With ActiveSheet
* * * * iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
* * *End With
* * ActiveCell.FormulaR1C1 = "Hello"
----------------------------------
I thought this would locate the last row and cell right after the one
that has data or not null from Colum A and place a hello in it. *Now
mind you the hello was just a test to see where the code directing the
action at. *It didn¹t work so I tried this next one

-----------------------------
Sub LastRow1()
Dim LastRow As Long
* * * * * *With Worksheets("Location")
* * LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
* * ActiveCell.FormulaR1C1 = "Hello"
End With
End Sub
-----------------------------
Still didn¹t work. *Now I am like totally confused. * Any help is
appreciated.

Ardy- Hide quoted text -

- Show quoted text -

thanks......
Works like acharm. I think part of my confusion is tha fact that I
don't quit undrestand ) "".End(xlUp).Offset(1, 0).Value "" or don't
get it. could you point me to some white papers that explains
this.....

Ardy

Last Row…./ last cell
 

On Wed, 11 Jun 2008 06:49:00 -0700 (PDT), Ardy wrote:

Hello All:
Searching the historical posts, I have found bunch of solutions that
will address the last row or maybe I should say locating the last row
in terms of holding data or not null. So with that I attempted this
code hoping that it will look and find the last cell in Colum A that
is not null and with that it assumes that the row is right after
that. So if A3 has “123W32M1” the code determines that A3 is the last
row and places the hello in A4. Of course I do realize that this
logic assumes that we always have data in Colum A.

Try #1
---------------------------------
Dim iLastRow As Integer
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
ActiveCell.FormulaR1C1 = "Hello"
----------------------------------
I thought this would locate the last row and cell right after the one
that has data or not null from Colum A and place a hello in it. Now
mind you the hello was just a test to see where the code directing the
action at. It didn’t work so I tried this next one

-----------------------------
Sub LastRow1()
Dim LastRow As Long
With Worksheets("Location")
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
ActiveCell.FormulaR1C1 = "Hello"
End With
End Sub
-----------------------------
Still didn’t work. Now I am like totally confused. Any help is
appreciated.

Ardy


Try this:

================================
Option Explicit
Sub LR()
Dim c As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
Set c = ws.Cells(ws.Rows.Count, 1).End(xlUp)

Set c = c.Offset(1, 0)

c.Formula = "HELLO"

End Sub
================================

or, if you really want to combine things: :-)

==========================
Sub LR2()
With Worksheets("Sheet1")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Formula = "HELLO"
End With
End Sub
============================
--ron

Last RowS�./ last cel
 

Since you're not very specific about what you don't understand, I'd
start with "Using With Statements" in VBA Help - that will explain the
"dot" convention, i.e., the "." in .End()

Both End() and Offset(), have VBA help topics, too.

In article
,
Ardy wrote:

thanks......
Works like acharm. I think part of my confusion is tha fact that I
don't quit undrestand ) "".End(xlUp).Offset(1, 0).Value "" or don't
get it. could you point me to some white papers that explains
this.....

Last Row…./ last cell
 

On Jun 11, 7:17*am, Ron Rosenfeld wrote:
On Wed, 11 Jun 2008 06:49:00 -0700 (PDT), Ardy wrote:
Hello All:
Searching the historical posts, I have found bunch of solutions that
will address the last row or maybe I should say locating the last row
in terms of holding data or not null. *So with that I attempted this
code hoping that it will look and find the last cell in Colum A that
is not null and with that it assumes that the row is right after
that. *So if A3 has “123W32M1” the code determines that A3 is the last
row and places the hello in A4. *Of course I do realize that this
logic assumes that we always have data in Colum A.

Try #1
---------------------------------
Dim iLastRow As Integer
* * With ActiveSheet
* * * *iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
* * End With
* *ActiveCell.FormulaR1C1 = "Hello"
----------------------------------
I thought this would locate the last row and cell right after the one
that has data or not null from Colum A and place a hello in it. *Now
mind you the hello was just a test to see where the code directing the
action at. *It didn’t work so I tried this next one

-----------------------------
Sub LastRow1()
Dim LastRow As Long
* * * * * *With Worksheets("Location")
* *LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
* *ActiveCell.FormulaR1C1 = "Hello"
End With
End Sub
-----------------------------
Still didn’t work. *Now I am like totally confused. * Any help is
appreciated.

Ardy

Try this:

================================
Option Explicit
Sub LR()
Dim c As Range
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")
Set c = ws.Cells(ws.Rows.Count, 1).End(xlUp)

Set c = c.Offset(1, 0)

c.Formula = "HELLO"

End Sub
================================

or, if you really want to combine things: *:-)

==========================
Sub LR2()
*With Worksheets("Sheet1")
* * .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Formula = "HELLO"
End With
End Sub
============================
--ron- Hide quoted text -

- Show quoted text -

Thnak you guys........
This is wonderfull. I think in retrospective all of the solutions are
good. I think in efforts of learning I need to read a bit more in the
VB help for what JE has pointed me to.

Thank you all