Relative reference? in macro

[TRYING]

I have a list of client names and locations in columns A and B respectively,
up to row 100. I recorded a macro that resorts the list by client name after
I add new data. My new data would be in rows 101 to 105.

Part of the code goes:
With ActiveWorkbook.Worksheets("ClientNames").Sort
.SetRange Range("A1:B100")
. Header . . .
. MatchCase
. etc

When recording the macro, here's how I selected the sort range--in tab
ClientNames, I went to cell A1 then clicked on the Select Current Range icon.
Did the sort and clicked on the Stop Recording button. I tried recording this
several times, clicking on the Use Relative Reference button at various
times. Didn't work. The macro limited the sort to rows 1 to 100 when I needed
it to sort rows 1 to 105.

I tried a different approach at selecting the sort range by going to cell
A1, then hitting the End and Downarrow keys to get to the last entry on the
list before clicking on the Select Current Range icon. Didn't work either.

This seems simple enough but I'm stumped. How can I make this work.

Thanks.

 

[Bernie Deitrick]

TRYING,

Replace

Range("A1:B100")
with

Range(Range("A1"),Range("B1").End(xlDown))

HTH,
Bernie
MS Excel MVP

 

[TRYING]

Forgot to mention that I'm using Excel 2007.

 

[TRYING]

Thanks, Bernie. It worked.

I also tried another solution. I edited the code and replaced "B100" with
"B1000" since I figure it would take a really long time before we reach row
1000. This one works too.

But your solution is exactly what I was trying to do, not just result-wise
but process-wise.

I'm just curious why I couldn't make my first approach work using macro
recording.