select last 20 cells

**asdzxc** · 04-30-2012, 03:54 AM

vba code for selecting last 20 cells, if cells less than 20, say10
select A2:B11.
plse see attached file.

**xld** · 04-30-2012, 04:46 AM

One very direct way

VBA:

Dim lastrow As Long 
Dim selectnum As Long 
 
lastrow = Cells(Rows.Count, "A").End(xlUp).Row 
Select Case True 
Case lastrow > 20:  selectnum = 20 
Case lastrow > 10:  selectnum = 10 
Case lastrow > 5:   selectnum = lastrow 
End Select 
 
Cells(lastrow - selectnum + 1, "A").Resize(selectnum).Select

VBA tags courtesy of www.thecodenet.com

**asdzxc** · 04-30-2012, 05:10 AM

Quote:

Originally Posted by: xld

One very direct way

VBA:

Dim lastrow As Long Dim selectnum As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row Select Case True Case lastrow > 20: selectnum = 20 Case lastrow > 10: selectnum = 10 Case lastrow > 5: selectnum = lastrow End Select Cells(lastrow - selectnum + 1, "A").Resize(selectnum).Select
VBA tags courtesy of www.thecodenet.com

thank you for quick reply.
your vba select only A11:A30 but I want A11:B30

**xld** · 04-30-2012, 06:52 AM

VBA:

Cells(lastrow - selectnum + 1, "A").Resize(selectnum, 2).Select

VBA tags courtesy of www.thecodenet.com

04-30-2012, 04:46 AM	#2
xld Distinguished Lord of VBAX VBAX Expert Joined: Apr 2005 Posts: 23,118 Kb Entries: 3 Articles: 2	One very direct way VBA: Dim lastrow As Long Dim selectnum As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row Select Case True Case lastrow > 20: selectnum = 20 Case lastrow > 10: selectnum = 10 Case lastrow > 5: selectnum = lastrow End Select Cells(lastrow - selectnum + 1, "A").Resize(selectnum).Select VBA tags courtesy of www.thecodenet.com ____________________________________________ Nihil simul inventum est et perfectum Abusus non tollit usum Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen! James Thurber
Local Time: 04:20 AM Local Date: 05-19-2013 Location:

04-30-2012, 06:52 AM	#4
xld Distinguished Lord of VBAX VBAX Expert Joined: Apr 2005 Posts: 23,118 Kb Entries: 3 Articles: 2	VBA: Cells(lastrow - selectnum + 1, "A").Resize(selectnum, 2).Select VBA tags courtesy of www.thecodenet.com ____________________________________________ Nihil simul inventum est et perfectum Abusus non tollit usum Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen! James Thurber
Local Time: 04:20 AM Local Date: 05-19-2013 Location: