Defining a variable for Ranges

[jdgreen]

I am working in Excel 2007. I am trying to define and select a range using a variable for the last row. The variable is defined (mouse over shows the right number), but I am getting an "application-defined or object-defined" error. I am new to VBA in Excel and doing anything with ranges and cells. The code is:
Dim LastRow As Long
LastRow = FindLastRow()
Sheets(ActiveSheet).Range(Cells(11, 1), Cells(13,LastRow)).Select

The FindLastRow() function is(which I found here):

Function FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
FindLastRow = r
End Function

John Green

 

[PHV]

Replace this:
Sheets(ActiveSheet).Range(Cells(11, 1), Cells(13,LastRow)).Select
with this:
With ActiveSheet
.Range(.Cells(11, 1), .Cells(13, LastRow)).Select
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

    Dim LastRow As Long
    LastRow = FindLastRow()[b]
'Cells(row, Column) is the correct syntax[/b]
    ActiveSheet.Range(ActiveSheet.Cells(1, 11), ActiveSheet.Cells(LastRow, 13)).Select

'The FindLastRow() function is(which I found here):[b]
'this is more correct if the used range does not start with row 1...[/b]
Function FindLastRow()
  dim r as long, c as integer
with ActiveSheet.UsedRange
    r = .Rows.Count + .row - 1
    c = .Columns.Count + .column - 1
end with
    FindLastRow = r
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

OOps, didn't realize the row/col mess.

With ActiveSheet
  .Range(.Cells(1, 11), .Cells(LastRow, 13)).Select
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886