Modify Macro to Include Inserted Rows
 

I recorded a macro to sort rows:

Sub test2()
'
' test2 Macro
'
'
Rows("2:5").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

If I insert rows between 2 and 5 the macro does not change to include
the new rows. How do I change it so it is not a fixed selection?

Chris.....

Modify Macro to Include Inserted Rows
 

Try this. This will find the last cell with data in it in Col. A and then
sort row 2 thru LastRow.

Option Explicit

Sub SortRows()

Dim LastRow As Long

LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
ActiveSheet.Rows("2:" & LastRow).Sort Key1:=Range("A2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub
--
Cheers,
Ryan


"Icefog" wrote:

I recorded a macro to sort rows:

Sub test2()
'
' test2 Macro
'
'
Rows("2:5").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

If I insert rows between 2 and 5 the macro does not change to include
the new rows. How do I change it so it is not a fixed selection?

Chris.....

Modify Macro to Include Inserted Rows
 

That almost does what I want it to. The macro provided assumes that
the sort will always being in at A2 (right?). How do I get the macro
to point to headings and words in the cells. For example:

NAME TEST 1 TEST 2
blank 2 7
Man 2 6
zebra 3 9

TOTALS 7 22

NAME TEST 1 TEST 2
able 3 5
cain 4 6
sport 2 7

TOTALS 9 18

I want to insert / delete rows between NAME and TOTALS and the macro
will expand to sort the names accordingly. And it would need to
happen multiple times on the same worksheet as evidenced above.

Chris.....

Modify Macro to Include Inserted Rows
 

Here is what I would do. This macro will sort rows that are below NAMES and
above TOTALS.

Option Explicit

Sub SortRows()

Dim rngNames As Range
Dim rngTotals As Range

' finds the cell with "NAMES"
Set rngNames = Sheets("Sheet1").Columns("A:A").Find(What:="NAMES" , _
After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

' finds the cell with "TOTALS"
Set rngTotals = Sheets("Sheet1").Columns("A:A").Find(What:="TOTALS ", _
After:=Cells(1, 1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


ActiveSheet.Rows(rngNames.Row + 1 & ":" & rngTotals.Row - 1).Sort
Key1:=Range("A2"), _

Order1:=xlAscending, _

Header:=xlGuess, _

OrderCustom:=1, _

MatchCase:=False, _

Orientation:=xlTopToBottom

End Sub

Hope this helps. If so please click yes below.
--
Cheers,
Ryan


"Icefog" wrote:

That almost does what I want it to. The macro provided assumes that
the sort will always being in at A2 (right?). How do I get the macro
to point to headings and words in the cells. For example:

NAME TEST 1 TEST 2
blank 2 7
Man 2 6
zebra 3 9

TOTALS 7 22

NAME TEST 1 TEST 2
able 3 5
cain 4 6
sport 2 7

TOTALS 9 18

I want to insert / delete rows between NAME and TOTALS and the macro
will expand to sort the names accordingly. And it would need to
happen multiple times on the same worksheet as evidenced above.

Chris.....