Selecting first and last row in a range

M

Mondi_2000

New Member
Joined
May 4, 2017
Messages
7
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 :rolleyes:
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]
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
What are you going to do with the range(s) you are selecting?
 
Upvote 0
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(n).Select
End Sub
 
Upvote 0
BobUmlas said:
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(n).Select
End Sub
Click to expand...
Here is another way you could have written your DoubleClick event code...
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]
 
Last edited:
Upvote 0
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

[TABLE="width: 594"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]ALDERSHOT[/TD]
[TD="align: left"]

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]MN 0 610[/TD]
[TD]JUNIOR RANK SLA (40 beds)[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]NN A 060[/TD]
[TD]VEHICLE GARAGING[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]BULFORD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]WI 0 625[/TD]
[TD]PACKAGE PLANTROOM[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]WI 0 626[/TD]
[TD]PACKAGE PLANTROOM[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]WI 0 627[/TD]
[TD]PACKAGE PLANTROOM[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]WI 0 613[/TD]
[TD]ARMOURY[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]WI 0 212[/TD]
[TD]VEHICLE GARAGING[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]LARKHILL[/TD]
[TD]

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tue 18-Apr-17[/TD]
[TD]LA 0 238[/TD]
[TD]JUNIOR & SENIOR OFFICER SLA[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LA 0 680[/TD]
[TD]JUNIOR & SENIOR OFFICER SLA (24 beds)[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LA 0 259[/TD]
[TD]VEHICLE GARAGING[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LA 0 254[/TD]
[TD]VEHICLE GARAGING[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LA H 003[/TD]
[TD]MILITARY VEHICLE HARDSTANDING[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LA 0 310[/TD]
[TD]VEHICLE GARAGING[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LX C 665[/TD]
[TD]PRIVATE PARKING (52 bays)[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]LA 0 636[/TD]
[TD]JUNIOR RANK SLA (48 beds)[/TD]
[TD]NB[/TD]
[/TR]
[TR]
[TD]Thanks Stirling[/TD]
[TD]

<tbody>
</tbody>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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
 
Upvote 0
Mondi_2000 said:
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
Click to expand...
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?

[TABLE="width: 500"]
<tbody>[TR]
[TD]1 London[/TD]
[TD]2017[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2 Data[/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]
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
233
Artik
Artik
H
  • Solved
ActiveSheet.Columns(ActiveCell.Column).Rows(3).Select ---- I'm trying to select multiple rows (cells) in the active column.
Replies
6
Views
468
jolivanes
jolivanes
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
494
etaf
etaf
H
  • Question Question
VBA Pivot Table Dynamic Range & Only One Selection per Row
Replies
3
Views
142
DanteAmor
DanteAmor
M
  • Question Question
Excel to create new rows of data in a table
Replies
1
Views
70
mumps
mumps

Forum statistics

Threads
1,223,099
Messages
6,170,109
Members
452,302
Latest member
TaMere

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top