rows........again (resolved - thank God!)

sykes · May 24th, 2002, 07:28 AM

Hi Guys
I'm trying to select a whole row, based on a value in a cell.
e.g. if the cell's value is 6, I want to select row 8, cell value 7, select row 9 etc etc.
It's simple I know, but driving me bonkers

Sykes

_________________
....and the meek shall inherit the earth...
(but not the mineral rights!)

[ This Message was edited by: sykes on 2002-05-24 07:56 ]

kkknie · May 24th, 2002, 07:51 AM

Assuming the selected row is always the input value + 2, this would work:



Dim iInput As Integer



iInput = Sheets("Sheet1").Range("A1").Value



Cells(iInput + 2, 1).EntireRow.Select

If the row to select is something different, you could use a select case construct.

K

[ This Message was edited by: kkknie on 2002-05-24 06:52 ]

Von Pookie · May 24th, 2002, 07:51 AM



Sub SelectRows()



If Range("A1") = "6" Then

    Rows("8:8").Select

    ElseIf Range("A1") = "7" Then

        Rows("9:9").Select

End If   

End Sub

Although kkknie's may be better for what you're trying to do...

_________________
~*Kristy*~

"Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam."

[ This Message was edited by: Von Pookie on 2002-05-24 06:53 ]

dimrod · May 24th, 2002, 08:03 AM

Or just one line of code :-

Rows(ActiveCell.Value + 2).Select

sykes · May 24th, 2002, 08:57 AM

You're far too clever for your own good.....all three of you!!

Many thanks, as always

Sykes

Mark W. · May 24th, 2002, 12:04 PM

BTW, a non-macro solution would be to define a Named range (e.g., Sheet1!row) as refers to...

=OFFSET(Sheet1!$A$1,Sheet1!$A$1+1,,1,255)

Now you can enter 6 into cell A1, press Control+G, and enter "row" as the Go To Reference and press [ Enter ].

[ This Message was edited by: Mark W. on 2002-05-24 11:05 ]

May 24th, 2002, 07:51 AM	#2
kkknie Board Regular Join Date: Apr 2002 Location: Greenwood, SC Posts: 677	Assuming the selected row is always the input value + 2, this would work: Dim iInput As Integer iInput = Sheets("Sheet1").Range("A1").Value Cells(iInput + 2, 1).EntireRow.Select If the row to select is something different, you could use a select case construct. K [ This Message was edited by: kkknie on 2002-05-24 06:52 ]

May 24th, 2002, 07:51 AM	#3
Von Pookie MrExcel MVP, Administrator Join Date: Feb 2002 Location: The act or process of locating. Posts: 13,679	Sub SelectRows() If Range("A1") = "6" Then Rows("8:8").Select ElseIf Range("A1") = "7" Then Rows("9:9").Select End If End Sub Although kkknie's may be better for what you're trying to do... _________________ ~Kristy~ "Catapultam habeo. Nisi pecuniam omnem mihi dabis, ad caput tuum saxum immane mittam." [ This Message was edited by: Von Pookie on 2002-05-24 06:53 ]

May 24th, 2002, 08:57 AM	#5
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	You're far too clever for your own good.....all three of you!! Many thanks, as always Sykes __________________ Sykes Vista / Xl2007

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 24th, 2002, 07:28 AM	#1
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	Hi Guys I'm trying to select a whole row, based on a value in a cell. e.g. if the cell's value is 6, I want to select row 8, cell value 7, select row 9 etc etc. It's simple I know, but driving me bonkers Sykes _________________ ....and the meek shall inherit the earth... (but not the mineral rights!) [ This Message was edited by: sykes on 2002-05-24 07:56 ]

May 24th, 2002, 08:03 AM	#4
dimrod Join Date: May 2002 Posts: 73	Or just one line of code :- Rows(ActiveCell.Value + 2).Select

May 24th, 2002, 12:04 PM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	BTW, a non-macro solution would be to define a Named range (e.g., Sheet1!row) as refers to... =OFFSET(Sheet1!$A$1,Sheet1!$A$1+1,,1,255) Now you can enter 6 into cell A1, press Control+G, and enter "row" as the Go To Reference and press [ Enter ]. [ This Message was edited by: Mark W. on 2002-05-24 11:05 ]