Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > How can change range to select active rows instead of :=Range("S10
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,081
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,081
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,081
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1,081
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 48
Default 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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using "=randbetween" to select a number from a range of cells a0xbjzz Excel Worksheet Functions 4 August 4th 07 08:47 PM
Select always same Rows range Nauj Solrac Excel Discussion (Misc queries) 2 April 26th 07 09:10 PM
Run-time error "1004" Select method of range class failed Tallan Excel Discussion (Misc queries) 3 March 7th 07 06:22 PM
Range("cell").Select Problem CWillis Excel Discussion (Misc queries) 2 June 5th 06 10:16 PM
range("a1").select not working tkaplan Excel Discussion (Misc queries) 3 October 27th 05 07:16 PM


All times are GMT +1. The time now is 05:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement