Range(Selection, Selection.End(xlUp)).Select to stop at row 2 and not include header rows.

[bensko]

I'm sure there is a simple solution to this, but I am having trouble finding the right one.

Any way to have Range(Selection, Selection.End(xlUp)).Select stop at row 3 and disregard the two header rows.

Something like Range(Selection, Selection.End(xlUp)-2).Select

Thanks in advance,

Ben

 

[xenou]

Hi,
I would just stop and check:

Range(Selection, Selection.End(xlUp)).Select
If Selection.Row < 3 Then
    Cells(3, Selection.Column).Select
End If

ξ

 

[xenou]

Note, or better (to preserve selection size):

Range(Selection, Selection.End(xlUp)).Select
If Selection.Row < 3 Then
    Selection.Offset(3 - Selection.Row, ).Select
End If

Test first - I may have the syntax wrong.

 

[xenou]

I did have the syntax wrong, and I think we want to resize the selection as well, so, try again:

Sub foo()

Range(Selection, Selection.End(xlUp)).Select
If Selection.Row < 3 Then
    Selection.Offset(3 - Selection.Row).Resize( _
        Selection.Rows.Count - (3 - Selection.Row), Selection.Columns.Count) _
        .Select
End If


End Sub

 

[bensko]

Sorry for my delayed appreciation - thank you!! This is exactly what I was looking for.

I did have the syntax wrong, and I think we want to resize the selection as well, so, try again:

Sub foo()

Range(Selection, Selection.End(xlUp)).Select
If Selection.Row < 3 Then
    Selection.Offset(3 - Selection.Row).Resize( _
        Selection.Rows.Count - (3 - Selection.Row), Selection.Columns.Count) _
        .Select
End If


End Sub