Need macro to work when new rows are inserted

[Joyce]

I have a worksheet that needs to be able to sort data 3
different ways:

I have created 3 different macros to perform these and
they work fine. However, a problem occurs when I want to
add more rows because the macro refers to a specific range
at the beginning.

Range("A5:I48").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
Key2:=Range("C3") _
, Order2:=xlAscending, Key3:=Range("D3"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

Sub Macro2()
'
' Macro2 Macro
' Sorts by Project Name/Price Date/Contract Type
'
' Keyboard Shortcut: Ctrl+n
'
Range("A5:I48").Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending,
Key2:=Range("A3") _
, Order2:=xlAscending, Key3:=Range("D3"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

Sub Macro3()
'
' Macro3 Macro
' Sorts by Contract Type/Price Date/Project Name
'
' Keyboard Shortcut: Ctrl+c
'
Range("A5:I48").Select
Selection.Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range("A3") _
, Order2:=xlAscending, Key3:=Range("C3"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

 

[Steve Yandl]

One option would be using the CurrentRegion property which will return the
range of cells that is part of a continuous region with the specified range.

Steve

 

[Joyce]

Please tell me in detail how I use the CurrentRegion
property.

 

[Mathew]

Hi Joyce,
I have encountered this, and I got round it by 'dynamic named ranges'
Have a look at DD's site http://www.contextures.com/xlNames01.html
and substitute A5:I48 as "your named range"
This will accommodate an ever increasing range, or inserted rows/columns
You may need to tinker a little, but at least it's a lead.
Yours,
Mathew
I have a worksheet that needs to be able to sort data 3
different ways:

I have created 3 different macros to perform these and
they work fine. However, a problem occurs when I want to
add more rows because the macro refers to a specific range
at the beginning.

Range("A5:I48").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending,
Key2:=Range("C3") _
, Order2:=xlAscending, Key3:=Range("D3"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

Sub Macro2()
'
' Macro2 Macro
' Sorts by Project Name/Price Date/Contract Type
'
' Keyboard Shortcut: Ctrl+n
'
Range("A5:I48").Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending,
Key2:=Range("A3") _
, Order2:=xlAscending, Key3:=Range("D3"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

Sub Macro3()
'
' Macro3 Macro
' Sorts by Contract Type/Price Date/Project Name
'
' Keyboard Shortcut: Ctrl+c
'
Range("A5:I48").Select
Selection.Sort Key1:=Range("D3"), Order1:=xlAscending,
Key2:=Range("A3") _
, Order2:=xlAscending, Key3:=Range("C3"),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

 

[Steve Yandl]

Joyce,

In the first macro you shared, replace
Range("A5:I48").Select
with
Range("A5").CurrentRegion.Select
and see if that doesn't accomplish what you want. If the region isn't
continuous or if there are adjacent cells you don't want, this may not do
the trick but I think it might.

Steve

 

[Guest]

This worked...thank you so much!

-----Original Message-----
Joyce,

In the first macro you shared, replace
Range("A5:I48").Select
with
Range("A5").CurrentRegion.Select
and see if that doesn't accomplish what you want. If the region isn't
continuous or if there are adjacent cells you don't want, this may not do
the trick but I think it might.

Steve





.