Select current selection to row 16

[trickyricky]

I know it's probably simple. I just need code to select the range of cells from my currently selected cell or selection up to row 16 for example.

Thanks in advance!

[shg]

    With Selection
        Range(.Cells(1), Cells(16, .Column + .Columns.Count - 1)).Select
    End With

[Colin Legg]

Hi,

Here is a simple, logical way to do that:

Sub foo()
    Const lngPIVOTROW As Long = 16    
    Dim lngFirstRow As Long
    Dim lngLastRow As Long
    
    If TypeOf Selection Is Range Then
        
        If Selection.Row < lngPIVOTROW Then
            lngFirstRow = Selection.Row
            lngLastRow = lngPIVOTROW
        
        ElseIf Selection.Row > lngPIVOTROW Then
            lngFirstRow = lngPIVOTROW
            lngLastRow = Selection.Row + Selection.Rows.Count - 1
            
        Else
            lngFirstRow = lngPIVOTROW
            lngLastRow = lngPIVOTROW
            
        End If
        
        Range( _
            Cells(lngFirstRow, Selection.Column), _
            Cells(lngLastRow, Selection.Column + Selection.Columns.Count - 1) _
                ).Select
        
    End If
        
End Sub

But perhaps all of this is unnecessary... if you give us the big picture we might be able to suggest a better approach?

[nimrod]

This command will deal with 2 dimensional selections i.e. multiColumn with multiRows ( i had trouble with shg's sol'n when in came to 2 dim selections) HOWEVER... the assumption is that the selection area is below row 16.

Union(Selection.Offset(16 - Selection.Row, 0), Selection).Select