Finding the last cell in a range
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:=xlDatab ase, SourceData:= _ "'RWA Data'!R1C1:R3502C19").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1" |
Finding the last cell in a range
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 -----Original Message----- 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" . |
Finding the last cell in a range
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) "Alex" wrote in message ... 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:=xlDatab ase, SourceData:= _ "'RWA Data'!R1C1:R3502C19").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1" |
Finding the last cell in a range
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 "Alex" wrote in message ... 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:=xlDatab ase, SourceData:= _ "'RWA Data'!R1C1:R3502C19").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1" |
All times are GMT +1. The time now is 05:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com