Series Code

[rickey24]

I have 2 columns of information, F & G. First, I want to Sort th
worksheet by Column G, then I wanted to re-order column G, startin
with the first row as "1" and filling down G with a series with a ste
of 1 (or 1, 2, 3, etc down G for all the rows until the last row wher
there is info in the corresponding F column).

When I record a Macro it gives me this code, but goes to a set G21
because in this particular case the last info in F was F21, bu
obviously this wouldn't always be the case. Thanks for all your help.

Bo


Rows("5:5").Select
Range("G5").Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("G5"), Order1:=xlAscending
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
_
DataOption1:=xlSortNormal
Range("G5").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
Range("G5:G21").Select
Range("G21").Activate
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear
Date:=xlDay, _
Step:=1, Trend:=Fals

 

[Trevor Shuttleworth]

Bo

try this:

Sub TestSeries()
With Range(Range("G5"), Range("G5").End(xlDown))
.Sort Key1:=Range("G5"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, _
Date:=xlDay, _
Step:=1, _
Trend:=False
End With
End Sub

Regards

Trevor

 

[pikus]

lcol = ActiveSheet.UsedRange.Column
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count
Range(Cells(1, 1), Cells(lrow, lcol)).Sort Key1:=Range("G1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
For x = 1 To lrow
Cells(x, 7).Value = x
Next x

Try that out. - Piku

 

[rickey24]

Works great, thanks, I appreciate it.

B