ExcelBanter - How can change range to select active rows instead of :=Range("S10

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - How can change range to select active rows instead of :=Range("S10 (https://www.excelbanter.com/excel-discussion-misc-queries/200602-how-can-change-range-select-active-rows-instead-%3Drange-s10.html)

ldiaz

How can change range to select active rows instead of :=Range("S10

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

Duke Carey

How can change range to select active rows instead of :=Range("S10

change this whole block of code
-------------------------------------------------------------------------
Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate
-------------------------------------------------------------------------

to this

Selection.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

Duke Carey

How can change range to select active rows instead of :=Range("S10

oops -

You'll need to leave in the part where you select the range to sort

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

ldiaz

How can change range to select active rows instead of :=Range(

but if the rows numbers changes, it will not work, I want to eliminate range:
S849
to select only active rows, it may be S849 or S860 or S839, I don't know..

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

oops -

You'll need to leave in the part where you select the range to sort

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

Duke Carey

How can change range to select active rows instead of :=Range(

OK, then
change this whole block of code
-------------------------------------------------------------------------
Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

to this

Set rng = Selection
rng.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"

set rng= rng.Offset(-1, -rng.Column + 1).Resize(rng.Rows.Count + 1,
rng.Column)

rng.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

"ldiaz" wrote:

but if the rows numbers changes, it will not work, I want to eliminate range:
S849
to select only active rows, it may be S849 or S860 or S839, I don't know..

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

oops -

You'll need to leave in the part where you select the range to sort

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

ldiaz

How can change range to select active rows instead of :=Range(

the formula:
=IF(G10<0,"1a","")

just appear in S10 field, I need it in all rows that has value in column R.

Thanks
LD

--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

OK, then
change this whole block of code
-------------------------------------------------------------------------
Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

to this

Set rng = Selection
rng.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"

set rng= rng.Offset(-1, -rng.Column + 1).Resize(rng.Rows.Count + 1,
rng.Column)

rng.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

"ldiaz" wrote:

but if the rows numbers changes, it will not work, I want to eliminate range:
S849
to select only active rows, it may be S849 or S860 or S839, I don't know..

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

oops -

You'll need to leave in the part where you select the range to sort

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

Duke Carey

How can change range to select active rows instead of :=Range(

Select all the rows before running the macro

"ldiaz" wrote:

the formula:
=IF(G10<0,"1a","")

just appear in S10 field, I need it in all rows that has value in column R.

Thanks
LD

--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

OK, then
change this whole block of code
-------------------------------------------------------------------------
Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

to this

Set rng = Selection
rng.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"

set rng= rng.Offset(-1, -rng.Column + 1).Resize(rng.Rows.Count + 1,
rng.Column)

rng.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

"ldiaz" wrote:

but if the rows numbers changes, it will not work, I want to eliminate range:
S849
to select only active rows, it may be S849 or S860 or S839, I don't know..

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

oops -

You'll need to leave in the part where you select the range to sort

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

ldiaz

How can change range to select active rows instead of :=Range(

I did that and it does not work, it adds the freeze incorrectly,
I added
Range("S10").Select
before Set rng = Selection and works better, but still have the same
problem, the formula =IF(G11<0,"1a","") is still in the field S10

please help.

Thanks
--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

Select all the rows before running the macro

"ldiaz" wrote:

the formula:
=IF(G10<0,"1a","")

just appear in S10 field, I need it in all rows that has value in column R.

Thanks
LD

--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

OK, then
change this whole block of code
-------------------------------------------------------------------------
Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

to this

Set rng = Selection
rng.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"

set rng= rng.Offset(-1, -rng.Column + 1).Resize(rng.Rows.Count + 1,
rng.Column)

rng.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

-------------------------------------------------------------------------

"ldiaz" wrote:

but if the rows numbers changes, it will not work, I want to eliminate range:
S849
to select only active rows, it may be S849 or S860 or S839, I don't know..

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

"Duke Carey" wrote:

oops -

You'll need to leave in the part where you select the range to sort

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

"ldiaz" wrote:

I have this macro, but if you see it selects S10:S849 as range, this way do
not work for me because some times the row numbers increase and decrease,

How can I change the range only to select active rows? even when rows
numbers may vary

Range("S10").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]<0,""1a"","""")"
Selection.AutoFill Destination:=Range("S10:S849")

Range("S10:S849").Select
Range("A9:S849").Select
Range("S9").Activate

Selection.Sort Key1:=Range("S10"), Order1:=xlDescending, Key2:=Range( _
"Q10"), Order2:=xlAscending, Key3:=Range("G10"),
Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal

Thanks
LD
--
Lorenzo DÃ*az
Cad Technician

All times are GMT +1. The time now is 02:27 AM.