Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset from end of a selection
I am trying to copy variable number of cells and then paste them. Then
I am trying to do an Offset form the last cell of the pasted cells. If I have more than one cell being pasted, the following works great: Selection.End(xlDown).Activate However, if there is only one cell being pasted, that syntax will activate the last cell in the column instead (cell #65536 at the bottom of the page). Here is a snippet with my attempt: Set compRow = Cells.Find(What:="component(s):", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Set zipRow = Cells.Find(What:="Zip File:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) ' selects rows of components between comprow and zip row Range(compRow.Offset(1, 0), zipRow.Offset(-2, 0)).Select Selection.Copy Sheets("Condensed ChangeLog").Select ActiveCell.Offset(0, 1).Select ActiveSheet.Paste Selection.End(xlDown).Activate Thank you for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset from end of a selection
Hi Vic.
To define the ranrge, try something like: '================ Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim iLastRow As Long Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row Set Rng = SH.Range("A1:A" & iLastRow) End Sub '<<================ --- Regards, Norman "Vic" wrote in message ups.com... I am trying to copy variable number of cells and then paste them. Then I am trying to do an Offset form the last cell of the pasted cells. If I have more than one cell being pasted, the following works great: Selection.End(xlDown).Activate However, if there is only one cell being pasted, that syntax will activate the last cell in the column instead (cell #65536 at the bottom of the page). Here is a snippet with my attempt: Set compRow = Cells.Find(What:="component(s):", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Set zipRow = Cells.Find(What:="Zip File:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) ' selects rows of components between comprow and zip row Range(compRow.Offset(1, 0), zipRow.Offset(-2, 0)).Select Selection.Copy Sheets("Condensed ChangeLog").Select ActiveCell.Offset(0, 1).Select ActiveSheet.Paste Selection.End(xlDown).Activate Thank you for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset from end of a selection
Vic,
Try: If Selection.Rows.Count 1 Then Selection.End(xlDown).Activate End If -- Hope that helps. Vergel Adriano "Vic" wrote: I am trying to copy variable number of cells and then paste them. Then I am trying to do an Offset form the last cell of the pasted cells. If I have more than one cell being pasted, the following works great: Selection.End(xlDown).Activate However, if there is only one cell being pasted, that syntax will activate the last cell in the column instead (cell #65536 at the bottom of the page). Here is a snippet with my attempt: Set compRow = Cells.Find(What:="component(s):", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Set zipRow = Cells.Find(What:="Zip File:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) ' selects rows of components between comprow and zip row Range(compRow.Offset(1, 0), zipRow.Offset(-2, 0)).Select Selection.Copy Sheets("Condensed ChangeLog").Select ActiveCell.Offset(0, 1).Select ActiveSheet.Paste Selection.End(xlDown).Activate Thank you for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset from end of a selection
On Apr 21, 3:56 pm, Vergel Adriano
wrote: Vic, Try: If Selection.Rows.Count 1 Then Selection.End(xlDown).Activate End If -- Hope that helps. Vergel Adriano "Vic" wrote: I am trying to copy variable number of cells and then paste them. Then I am trying to do an Offset form the last cell of the pasted cells. If I have more than one cell being pasted, the following works great: Selection.End(xlDown).Activate However, if there is only one cell being pasted, that syntax will activate the last cell in the column instead (cell #65536 at the bottom of the page). Here is a snippet with my attempt: Set compRow = Cells.Find(What:="component(s):", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) Set zipRow = Cells.Find(What:="Zip File:", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) ' selects rows of components between comprow and zip row Range(compRow.Offset(1, 0), zipRow.Offset(-2, 0)).Select Selection.Copy Sheets("Condensed ChangeLog").Select ActiveCell.Offset(0, 1).Select ActiveSheet.Paste Selection.End(xlDown).Activate Thank you for any help.- Hide quoted text - - Show quoted text - Thank you. This worked well! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
type missmatch on Selection.Offset | Excel Programming | |||
Offset and Range Selection | Excel Programming |