Shrink selected range by one row 1

[agale]

Trying to reduce the size of a selected range by backing off the last row. The range has been selected using an Application.GoTo Reference:="area" so the range boundries are not known. For example if the selected range is A75:K85 I want to change the range to A75:K84.

Thanks

 

[kmfna]

This looks like Excel VBA, am I wrong? If so, I'm not sure how your macro works, but can you take the second argument for the range and simply subtract 1 from it? It may not be that easy, but I'm sure you could cut the numeric value off of the range, subract 1 and resubmit the new number.

Good luck,
Kevin

- "The truth hurts, maybe not as much as jumping on a bicycle with no seat, but it hurts.

 

[johnwm]

To get the start cells, use rangename.cells(1,1) and end cells with rangename.rows.count and columns.count, try something like:

With ActiveSheet.Range("testarea1")
r1 = .Cells(1, 1).Row
c1 = .Cells(1, 1).Column
r2 = .Rows.Count + r1
c2 = .Columns.Count + c1
End With
a = (Chr(64 + c1) & r1 & ":" & Chr(63 + c2) & (r2 - 2))
Range(a).Select

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[agale]

Thanks johnwm - worked perfectly. Much appreciate you helping out a VBA neophyte. Have a great weekend.