Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |