Using a calculated vale to define a range

[Guest]

I need to select a sort range to include every row containing data, but no more. I've calculated the last row containing data using the COUNTA(A$:A$) function but how do I use this in defining the sort range
eg. the range is A7:F? where ? is the calculated number. I need this to be dynamic so that it automatically updates in the macro as each new row of data is added

This is the macro as it stands for a set range. I need the F23 value to be dynamic

Range("A7:F23").Selec
Selection.Sort Key1:=Range("A7"), Order1:=xlAscending, Key2:=Range("B7")
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=
xlSortNorma
Range("B2").Selec
End Sub

Thanks
Ken G.

 

[Guest]

I've figured this out by myself too! (Feeling good!
I used the OFFSET command as below .

Range("A7ffset(A6,F1-6,6)").Selec

where F1 has the value calculated in the spreadsheet by the COUNTA function and -6 allows for rows above the data and the final "6" selects column "F

Ken G.

 

[Bob Phillips]

cLastRow = Celle(Rows.Count,"A").End(xlUp).Row
Set rng =Range("A7:A" & cLastRow)
rng.Sort Key1:=Range("A7"), Order1:=xlAscending, Key2:=Range("B7") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=
_
xlSortNormal
Range("B2").Select
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I need to select a sort range to include every row containing data, but no

more. I've calculated the last row containing data using the COUNTA(A$:A$)
function but how do I use this in defining the sort range?

eg. the range is A7:F? where ? is the calculated number. I need this to

be dynamic so that it automatically updates in the macro as each new row of
data is added.

 

[JMay]

Bob, your: Line
Set rng =Range("A7:A" & cLastRow)

wouldn't (shouldn't) that be

Set rng =Range("A7:F" & cLastRow)

??
JMay

 

[Bob Phillips]

Indeed it should sir. Thanks for the spot.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JMay]

Sorry bob, but I think too often my greatest contribution is that I can spot
only the "little-issues" while totally missing the "BIG issues" or how to
achieve the really BIG Stuff; Hopefully that will change, in time (??).
Thanks for your help,
from all of us.