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

L

ldiaz

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
 
D

Duke Carey

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
 
D

Duke Carey

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
 
L

ldiaz

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Duke Carey

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

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 said:
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 said:
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 said:
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



:

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
Click to expand...
Click to expand...
Click to expand...
 
D

Duke Carey

Select all the rows before running the macro

ldiaz said:
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 said:
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 said:
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


:

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



:

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
Click to expand...
Click to expand...
Click to expand...
 
L

ldiaz

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 said:
Select all the rows before running the macro

ldiaz said:
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 said:
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


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

:



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


:

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



:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with input box to select a sort up to 28 1
Macro to sort variable data range fails 1
Code for variable sort order 3
Macro not working properly when run automatically 9
Run-Time error '1004' : Select method of Range class failed 3
VBA Variable Range Sort 4
Run VBA sort when I open worksheet 2
Need macro to work when new rows are inserted 5

Top