Thread: Auto expand range based on first cell selection

Hi

I need some help in selecting a long, but single column array of data. Currently I am using Application.InputBox to allow the user to select the required range. This works but is a tedious process as it must often be repeated and there can be up to 20,000 rows to scroll down.

Is it possible to have the user select the first cell of the appropriate column then have VBA expand the rows of the range down, to including the remaining values? Please note that the data needed is not always in the same column, beginning on the same row or is the same length so any solution must be fairly generic.

Thanks!

Have you tried selecting the first cell, then using the arrow keys while holding down Ctrl+Shift?

f8 (extended selection) can also be handy

You could put this in the worksheet module but it will override everything so controlling it will be a problem

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range(Selection, Selection.End(xlDown)).Select
End Sub

Probably better to use individual macros, here are two examples

Sub ExtendRangeDown()
    Range(Selection, Selection.End(xlDown)).Select
End Sub

Sub ExtendRangeRight()
    Range(Selection, Selection.End(xlToRight)).Select
End Sub

Hope this helps