Range names in Macros

[Nick]

If i have the following code to sort two columns linked as
a macro...

Sheets("low").Select
Range("B4:B15").Select
Selection.sort Key1:=Range("B4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("C4:C15").Select
Selection.sort Key1:=Range("C4"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

If i want to later change this range, it is obviously
better to have a range name. how would you code this into
VB? would you also have a range name for the sorting
criteria?

cheers
nick

 

[Josie Dethiers]

I would suggest to give a name only to the line of headers (the whole line
so columns can be added or removed).
Range("headers").CurrentRegion.Select

Before sorting, I would search for this range as I would search for the
cells which contain the sorting criteria. I would either name these cells
or search for the text they contain.
This also allows to add or remove columns later on.

Set CellFound = Range("headers").find(...)
If Not CellFound is Nothing then
Selection.Sort ...
End If

 

[Dave Peterson]

I'm not sure it's always better to have a range name. Sometimes it's easier to
just find the range in your macro.

It looks like you might be sorting individual columns in your worksheet. Your
code sorts columns B&C starting in row 4. I guessed that you wanted to use the
last row used in each column (but this might not be close to what you really
wanted!):

I like to start at the bottom of the column (like X65536 and go up until I hit a
filled in cell to find the last used cell in that column. (Alternatively, you
could start near the top and go down.) I think it's safer when the data has
gaps in that column.)

Anyway:

Option Explicit
Sub testme()

Dim iCol As Long
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("low")

With wks
For iCol = 2 To 3
Set myRng = .Range(.Cells(4, iCol), _
.Cells(.Rows.Count, iCol).End(xlUp))
With myRng
.Sort key1:=.Cells(1, 1), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
Next iCol
End With

End Sub

(And you know if row 4 is the header or not. I'd remove all doubt and put xlyes
or xlno.)