Finding the last cell in a range

[Alex]

I have a few macros that select a range of cells. I
occassionally delete the contents of the RWA Data sheet
and paste in new information that contains the same number
of columns, but the number of rows will always change.
The follwing is an example of some code from one of my
macros. I will always be selecting " 'RWA Data' !R1C1:
but the last row will change. What code can I add that
will look at the last cell in a list that my cursor is in?

Thanks much,
Alex


Range("E14").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"'RWA Data'!R1C1:R3502C19").CreatePivotTable
TableDestination:="", TableName _
:="PivotTable1"

 

[Andres Angel]

Do as follow:
Use a column that will have value in every row.
Assume column A
range("A2").select
ActiveCell.End(xlDown).Select
LastRow = ActiveCell.Row
SourceData:= _

"'RWA Data'!R1C1:R" & LastRow

& "C19").CreatePivotTable
range("A2').select

This should work

Andres

 

[Bob Phillips]

Get last row with

cRow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Paul Lautman]

Use the following function:
Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function

then LastCell('RWA Data').row should give you the last row that you require