Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative reference? in macro
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative reference? in macro
TRYING,
Replace Range("A1:B100") with Range(Range("A1"),Range("B1").End(xlDown)) HTH, Bernie MS Excel MVP "TRYING" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative reference? in macro
Forgot to mention that I'm using Excel 2007.
"TRYING" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Relative reference? in macro
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. "Bernie Deitrick" wrote: TRYING, Replace Range("A1:B100") with Range(Range("A1"),Range("B1").End(xlDown)) HTH, Bernie MS Excel MVP "TRYING" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro relative reference toolbar disappeared | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
how do I change macro relative reference? no stop recording tool | Excel Discussion (Misc queries) | |||
Using relative reference in macro | Excel Discussion (Misc queries) | |||
Recording new excel macro, relative reference button doesn't show | New Users to Excel |