Last Row…./ last cell

[Ardy]

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

 

[JE McGimpsey]

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.

 

[Gary''s Student]

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.

 

[Ardy]

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.









- 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

 

[Ron Rosenfeld]

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

 

[JE McGimpsey]

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.

 

[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