LastRow issue

  • Thread starter Thread starter Anne
  • Start date Start date
A

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
 
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
 
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
 
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
Click to expand...
mean by "It is working only in certain places."
Best regards
Anders Silvén

"Anne" <[email protected]> skrev i
Click to expand...
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
Click to expand...
.
Click to expand...
 
Back
Top