Selecting first and last row in a range

[Mondi_2000]

Hi Forum,
I am trying to select a range of rows in a spreadsheet, via a macro.
My macro button is in the London 2017 cell.
Once I click on the button I'd like the macro to select Rows 2 to 5
Rows 2-5 are empty so I'm sure the code will include Select XLDown but that selects Row 6
I would like to use the same code to be able to select Rows 7 to 8 etc etc
I've looked at so much code but I've not found anything to help me.. I'm not trained in VBA so simple is best
Thank you in advance
Raymond

1 London	2017	2018
2	10	20
3	10	20
4	10	20
5	10	20
6 New York	2017	2018
7	10	20
8	10	20
9 Sydney	2017	2018

<tbody>
</tbody>

 

[Norie]

What are you going to do with the range(s) you are selecting?

 

[BobUmlas]

Right-click the sheet tab, select View Code, paste this code in. It works by double-clicking "London" or "New York" or "Sydney" etc.
No buttons necessary.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = "" Then Exit Sub
lastused = Range("B1000000").End(xlUp).Row
rw = Target.Row
rw2 = Target.End(xlDown).Row - 1
Target.Offset(1).Resize(rw2 - rw).Select
n = lastused - rw
If n = 0 Then n = 1
If Selection.Rows.Count > 100000 Then Target.Offset(1).Resize.Select
End Sub

 

[Mondi_2000]

What are you going to do with the range(s) you are selecting?

I am then going to sort the data using SORT (which I can do)

Thanks

 

[Rick Rothstein]

Right-click the sheet tab, select View Code, paste this code in. It works by double-clicking "London" or "New York" or "Sydney" etc.
No buttons necessary.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = "" Then Exit Sub
lastused = Range("B1000000").End(xlUp).Row
rw = Target.Row
rw2 = Target.End(xlDown).Row - 1
Target.Offset(1).Resize(rw2 - rw).Select
n = lastused - rw
If n = 0 Then n = 1
If Selection.Rows.Count > 100000 Then Target.Offset(1).Resize.Select
End Sub

Here is another way you could have written your DoubleClick event code...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim LastUsedRow As Long
  If Target.Value = "" Or Target(1).Offset(1) <> "" Then Exit Sub
  LastUsedRow = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
  If Target.Row < LastUsedRow Then Range(Target, Cells(LastUsedRow, Target.Column)).SpecialCells(xlBlanks).Areas(1).EntireRow.Select
End Sub[/td]
[/tr]
[/table]

 

[Mondi_2000]

Thanks for the code...However:

1) The code doesn't select the entire row, only those cells in the the first column
2) It fails if there is data in the first cell below London, New York, Sydney

Apologies but I suppose I should have added factor 2) into the Query.

The actual data is below...
Select 2 rows for Aldershot
Select 5 rows for Bulford
Select 8 rows for Larkhill (including the row that contains Tue 18-April-17

ALDERSHOT	<tbody> </tbody>
	MN 0 610	JUNIOR RANK SLA (40 beds)	NB
	NN A 060	VEHICLE GARAGING	A
BULFORD
	WI 0 625	PACKAGE PLANTROOM	NB
	WI 0 626	PACKAGE PLANTROOM	NB
	WI 0 627	PACKAGE PLANTROOM	NB
	WI 0 613	ARMOURY	NB
	WI 0 212	VEHICLE GARAGING	A
LARKHILL	<tbody> </tbody>
Tue 18-Apr-17	LA 0 238	JUNIOR & SENIOR OFFICER SLA	A
	LA 0 680	JUNIOR & SENIOR OFFICER SLA (24 beds)	NB
	LA 0 259	VEHICLE GARAGING	A
	LA 0 254	VEHICLE GARAGING	A
	LA H 003	MILITARY VEHICLE HARDSTANDING	NB
	LA 0 310	VEHICLE GARAGING	A
	LX C 665	PRIVATE PARKING (52 bays)	NB
	LA 0 636	JUNIOR RANK SLA (48 beds)	NB
Thanks Stirling	<tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Mondi_2000]

Rick, that works great, thanks but... if there is data in the first cell below London etc, then it doesn't work .. can you please update the code to incorporate this.. they will be times when there is no data, and other times when data will be in rows, as per the above

 

[Rick Rothstein]

Rick, that works great, thanks but... if there is data in the first cell below London etc, then it doesn't work .. can you please update the code to incorporate this.. they will be times when there is no data, and other times when data will be in rows, as per the above

Do you mean like what I show in red below? If so, and I double click the word "London", what rows should be selected, 2:5 or 3:5?

1 London	2017	2018
2 Data	10	20
3	10	20
4	10	20
5	10	20
6 New York	2017	2018
7	10	20
8	10	20
9 Sydney	2017	2018

<tbody>
</tbody>

 

[Mondi_2000]

Yes, if there is DATA in 2, then select 2:5.. then I click on New York and it selects 7:8 (even if there is no data in 7...)
I have code for the SORT already.. can I paste that after the code for the Row selection?

 

[Mondi_2000]

Rick, I have just discovered that your formula works perfectly well, as I simply click on the 'DATA' cell (which itself doesn't need sorting) and the formula selects the remaining rows until the next heading - excellent.
My problem is now, as I have just discovered, that the SORT VBA details uses row references (even if I click on relative reference), so is there a way to just say 'with selection' and not mention the first and last rows?
Thanks,
Raymond