Sleeper: Selecting row through active cell

[russkie]

Hey,
ehm... im pretty sure this is a simple one and stupid, but i couldnt find how to do it.

How do i select the row that the ActiveCell is in?
Oh and... this smiley is cool::rotlaugh:

[Zack Barresse]

Hi there,

You want to select the entire row? If so, like this ...


Rows(ActiveCell.Row).Select

I'm a little confused as to why you want to Select something though. It's generally not needed to manipulate data in code and is usually for asthetic purposes only. Is there some reason to do this? Or is this for an underlying issue(s)?

[TonyJollans]

Yup, it's easy :)


ActiveCell.EntireRow.Select

(or Shift+Spacebar in the GUI)

[russkie]

um..

well i have to search a column for a variable. Now once i find it i need to get the Row in which the variable is in. THEN i need to edit the first and last cell of that row. so uh.. besides going through selections how would you go about doing that??

[Zack Barresse]

By 'first and last cell of that row', do you mean the cell in column A of that row, and the last one containing data in that row?

[russkie]

yup

[Zack Barresse]

Well, here is an example. There are a few assumptions lined out in comments ...


Option Explicit

Sub FindSomething()
Dim myVariable As Variant
Dim Cell1 As Range, Cell2 As Range
Dim ws As Worksheet
Dim rngLook As Range, rngFound As Range
Dim LastCol As Long
myVariable = "def"
Set ws = ActiveSheet '<< set to whatever sheet desired
Set rngLook = ws.Range("B:B") '<< assuming column B
Set rngFound = rngLook.Find(myVariable, LookIn:=xlValues, MatchCase:=True)
If rngFound Is Nothing Then
MsgBox "Sorry, but it wasn't found."
Else
Set Cell1 = ws.Cells(rngFound.Row, 1)
Set Cell2 = ws.Cells(rngFound.Row, 256).End(xlToLeft)
Cell1.Value = Cell1.Value + 1 'assuming numerical
Cell2.Value = Cell2.Value + 1 'assuming numerical
End If
End Sub


Edit: hehe, 2 minutes. I think that's a record for me. :D

[russkie]

...

very cool man, this'll help alot and save a buncha code. thanks alot.
and also, the smiley::rotlaugh:

[mdmackillop]

Hi Russkie,
Have a look at the following. It will search column A, and then ask for input which will be entered in columns A & C corresponding to the found value.
Regards
MD


Sub EditCells()
Dim ToFind
Dim SearchCol As String
Dim Found As Range
SearchCol = "A"
ToFind = InputBox("Enter data to find")
Set Found = Columns(SearchCol).Find(What:=ToFind, After:=[A1], lookat:=xlWhole)
If Found Is Nothing Then GoTo Finish
'Edit cells
Found.Range("A1").Formula = InputBox("Enter new start text", , Found.Range("A1").Formula)
Found.Range("C1").Formula = InputBox("Enter new end text", , Found.Range("C1").Formula)
Found.Select
Exit Sub
Finish:
MsgBox "Text not found"
End Sub

[mdmackillop]

Beat me by a whisker Zack!

[Zack Barresse]

LOL! Still lookin good MD!! There's always [way] more than one way to skin these feline's. :D