Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
An old chestnut: finding the last cell in a range | Excel Worksheet Functions | |||
Finding Cell Outside Of Range | Excel Worksheet Functions | |||
Finding the bottom non-blank cell in a range | Excel Discussion (Misc queries) | |||
Finding first (end of range) empty cell | Excel Programming | |||
Finding if a cell is within a Range | Excel Programming |