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.

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.

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.

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!