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


[TABLE="width: 500"]
<tbody>[TR]
[TD]1 London[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]6 New York[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]9 Sydney[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

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...)

Okay, I am a little unsure about your data layout...

1) In Message #1, you show more data on the same row as your Column A entries; however, in Message #6, you show the Column A items on the row all by themselves (no other data on the row). Which is it (the actual arrangement of your data dictates how to write the code)?

2) In Message #6, you show a blank row under the LARKHILL item... do you actually have such blank rows scattered throughout your data?

 

[Mondi_2000]

Rick, the blank row under LARKHILL is an error.. the layout is as per #6.
However, the VBA you previously provided does work, as long as I click in the cell that has the last complete entry in it - this selects data to the last blank cell and selects the appropriate rows (from top to bottom).
The next stage is to sort the Rows by date (which is in another column) but the code I have (recorded in a macro) embeds the row numbers in it.
What I need is to have excel select the rows at the end of your code and with those rows selected perform the sort... I can't have the rows numbers embedded in the VBA, as it won't work on other parts of the spreadsheet.
It used to be called "with.selection" or similar.
Thanks Rick