VBA, Excel - Find Range, Sort
 

Please correct my macro:

Application.Goto Reference:="AAA"
ActiveCell.Resize(xlDown, 9).Select

I'm trying to write a macro to find a named range, then highlight the rows
(?) down to the last cell and to the right (col) 9 columns so I can sort it
ascending (header is AAA).

I can copy the macro for the sorting by doing it step by step but I don't
know how select the range.

Thanks you.

Danny,

xlDown is an Excel constant, and is a negative number, -4121 to be exact.
Passing a negative number is invalid as you cannot have a negative numnber
of rows.

I think you are looking for something like

With Activecell
iLastRow = cells(rows.Count,.Column).end(xlup).Row
Set rng = .Resize(ilastrow - .Row + 1, 9)
End With
rng.Sort etc.

--
HTH

Bob Phillips

"Danny" wrote in message
...
Please correct my macro:

Application.Goto Reference:="AAA"
ActiveCell.Resize(xlDown, 9).Select

I'm trying to write a macro to find a named range, then highlight the rows
(?) down to the last cell and to the right (col) 9 columns so I can sort
it
ascending (header is AAA).

I can copy the macro for the sorting by doing it step by step but I don't
know how select the range.

Thanks you.

Hi Bob, thanks for your prompt response. I copied your formula and for some
reason did not work. Please write me another macro just so I can go to the
named range and highlight the rows(???) and the 9 columns from the named
range.

Thanks again.

"Bob Phillips" wrote:

Danny,

xlDown is an Excel constant, and is a negative number, -4121 to be exact.
Passing a negative number is invalid as you cannot have a negative numnber
of rows.

I think you are looking for something like

With Activecell
iLastRow = cells(rows.Count,.Column).end(xlup).Row
Set rng = .Resize(ilastrow - .Row + 1, 9)
End With
rng.Sort etc.

--
HTH

Bob Phillips

"Danny" wrote in message
...
Please correct my macro:

Application.Goto Reference:="AAA"
ActiveCell.Resize(xlDown, 9).Select

I'm trying to write a macro to find a named range, then highlight the rows
(?) down to the last cell and to the right (col) 9 columns so I can sort
it
ascending (header is AAA).

I can copy the macro for the sorting by doing it step by step but I don't
know how select the range.

Thanks you.

Hi Bob,

Sorry, sorry, your formula worked perfectly! I think when I copied and
pasted it something else was copied pasted with it. I "cleaned" the macro and
it work perfectly.

Thanks a million!

"Danny" wrote:

Hi Bob, thanks for your prompt response. I copied your formula and for some
reason did not work. Please write me another macro just so I can go to the
named range and highlight the rows(???) and the 9 columns from the named
range.

Thanks again.

"Bob Phillips" wrote:

Danny,

xlDown is an Excel constant, and is a negative number, -4121 to be exact.
Passing a negative number is invalid as you cannot have a negative numnber
of rows.

I think you are looking for something like

With Activecell
iLastRow = cells(rows.Count,.Column).end(xlup).Row
Set rng = .Resize(ilastrow - .Row + 1, 9)
End With
rng.Sort etc.

--
HTH

Bob Phillips

"Danny" wrote in message
...
Please correct my macro:

Application.Goto Reference:="AAA"
ActiveCell.Resize(xlDown, 9).Select

I'm trying to write a macro to find a named range, then highlight the rows
(?) down to the last cell and to the right (col) 9 columns so I can sort
it
ascending (header is AAA).

I can copy the macro for the sorting by doing it step by step but I don't
know how select the range.

Thanks you.