Selecting a large number of rows, but not all

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a spreadsheet with almost 10,000 rows that I am going to do a lot of
sorts and subtotals on. I want to be able to start near the bottom row but
not the absolute bottom one and and select up to almost the top row but not
the absolute top. Aside from holding down the left mouse button and waiting
for it to scroll up all of the rows, is there a shortcut other than "select
all" to start at one row and have it select everything between that and
another row in one quick step?

Thank you,
Katherine
 
maybe this idea will help. Modify to suit
Sub rowstoseletct()
X = InputBox("1st row?")
y = InputBox("lastrow")
Rows(X & ":" & y).Select
End Sub
 
Get familiar with the "Name Box" (left of formula bar).

Click in starting cell (A1),
Click in name box,
Enter ending cell address (Z1000),
Hold down <Shift> and hit <Enter>

OR

Click in name box,
Enter range (A1:Z1000)
Hit <Enter>
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,

I have a spreadsheet with almost 10,000 rows that I am going to do a lot of
sorts and subtotals on. I want to be able to start near the bottom row but
not the absolute bottom one and and select up to almost the top row but not
the absolute top. Aside from holding down the left mouse button and waiting
for it to scroll up all of the rows, is there a shortcut other than "select
all" to start at one row and have it select everything between that and
another row in one quick step?

Thank you,
Katherine
 
Katherine

If you know the start and end points(rows) you can type that range in the Name
Box at left side of Formula Bar and hit ENTER key.


i.e. A5:A9982 for one column selection or A5:K9982 for multiple column
selection.


Gord Dibben Excel MVP
 
Hi Katherine,
If you want a definite answer you have to ask a very definite question.
You can use the following macro to take you to the last used cell
in the current column. The macro starts at the bottom of the column
and then looks upward to find a non blank cell.

Sub GotoBottomOfCurrentColumn()
'Tom Ogilvy 2000-06-26
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
End Sub


If you want to put that on your toolbar see
http://www.mvps.org/dmcritchie/excel/toolbars.htm

You could then manually fill in the name box as already mentioned
by choosing the row numbers you actually want to select i.e. 2:9999
the name box is a the left of the formula bar also as already mentioned.

Obviously if you gave more information, you could select something
like the row above that up through to row 2 as in the following modification...
This macro will look in Column A and will exclude to the row and the
last row used in Column A.

Sub AllButTopAndBottom()
'based on a technique posted 2000-06-26 by Tom Ogilvy
Range("2:" & Cells(Rows.Count, 1).End(xlUp).Offset(-1, 0).row).Select
End Sub

For more information on installng and using a macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top