LastRow issue

[Anne]

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

 

[Anders S]

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

 

[Bob Kilmer]

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

 

[Narayana M Nerella]

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" <[email protected]> skrev i

meddelandet news:[email protected]...

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

.