Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastRow issue
I am having a problem with the LastRow function. It is
working only in certain places. I am trying to use this function on a summary page that varies everytime I import a file. From this summary page, I am creating a chart. I have tried various ways of doing this with no luck. Any Suggestions? My code is below. Thanks in advance! Anne Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Range("A6").Select Row_Num = ActiveCell.Row Set wks = ActiveSheet LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row With wks .Range("A6").FormulaR1C1 = "=RC[5]/LastRow" .Range("A6").AutoFill _ Destination:=.Range("A6:A" & LastRow), Type:=xlFillCopy End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastRow issue
Anne,
For what it's worth... In your code, LastRow is a variable, not a function. The first part of the code sets the value of LastRow correctly as far as I can see. In the second part of your code, I can only make it work if I change ..Range("A6").FormulaR1C1 = "=RC[5]/LastRow" to ..Range("A6").FormulaR1C1 = "=RC[5]/" & LastRow Don't know what you are trying to do, and what do you mean by "It is working only in certain places." Best regards Anders Silvén "Anne" skrev i meddelandet ... I am having a problem with the LastRow function. It is working only in certain places. I am trying to use this function on a summary page that varies everytime I import a file. From this summary page, I am creating a chart. I have tried various ways of doing this with no luck. Any Suggestions? My code is below. Thanks in advance! Anne Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Range("A6").Select Row_Num = ActiveCell.Row Set wks = ActiveSheet LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row With wks .Range("A6").FormulaR1C1 = "=RC[5]/LastRow" .Range("A6").AutoFill _ Destination:=.Range("A6:A" & LastRow), Type:=xlFillCopy End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastRow issue
To identify the last row, I use something like this.
Option Explicit Public Function LastOccupiedRow(rng As Range) As Range 'Returns Nothing if range is empty. Set LastOccupiedRow = rng.Find( _ What:="*", _ LookIn:=xlValues, _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows) End Function Sub DemoUsingIt() Dim lastRow As Range Set lastRow = LastOccupiedRow(ActiveSheet.Columns("A")) If Not lastRow Is Nothing Then MsgBox lastRow.Address End If End Sub -- Bob Kilmer "Anne" wrote in message ... I am having a problem with the LastRow function. It is working only in certain places. I am trying to use this function on a summary page that varies everytime I import a file. From this summary page, I am creating a chart. I have tried various ways of doing this with no luck. Any Suggestions? My code is below. Thanks in advance! Anne Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Range("A6").Select Row_Num = ActiveCell.Row Set wks = ActiveSheet LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row With wks .Range("A6").FormulaR1C1 = "=RC[5]/LastRow" .Range("A6").AutoFill _ Destination:=.Range("A6:A" & LastRow), Type:=xlFillCopy End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastRow issue
sub test()
dim x as integer x=FindLastRowOfTheSheet("Sheet1") end sub 'written by NARYANA MURTHY NERELLA Function FindLastRowOfTheSheet(ShName As String) As Integer FindLastRowOfTheSheet = ActiveWorkbook.Sheets (ShName).Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row End Function -----Original Message----- Anne, For what it's worth... In your code, LastRow is a variable, not a function. The first part of the code sets the value of LastRow correctly as far as I can see. In the second part of your code, I can only make it work if I change ..Range("A6").FormulaR1C1 = "=RC[5]/LastRow" to ..Range("A6").FormulaR1C1 = "=RC[5]/" & LastRow Don't know what you are trying to do, and what do you mean by "It is working only in certain places." Best regards Anders Silvén "Anne" skrev i meddelandet ... I am having a problem with the LastRow function. It is working only in certain places. I am trying to use this function on a summary page that varies everytime I import a file. From this summary page, I am creating a chart. I have tried various ways of doing this with no luck. Any Suggestions? My code is below. Thanks in advance! Anne Dim LastRow As Long Dim wks As Worksheet Dim Row_Num As Integer Range("A6").Select Row_Num = ActiveCell.Row Set wks = ActiveSheet LastRow = Cells(Cells.Rows.Count, "A").End (xlUp).Row With wks .Range("A6").FormulaR1C1 = "=RC[5]/LastRow" .Range("A6").AutoFill _ Destination:=.Range("A6:A" & LastRow), Type:=xlFillCopy End With . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lastrow | Excel Worksheet Functions | |||
Lastrow | Excel Worksheet Functions | |||
HELP Lastrow Range | Excel Discussion (Misc queries) | |||
LastRow of Data | Excel Worksheet Functions | |||
Lastrow in Range | Excel Discussion (Misc queries) |