Highlite from A2 to Last Fred then to Column G

[Steved]

Hello from Steved

Using the Below to find the last name Fred in Col A I would like please to
have the cursor in Cell A2 then Highlite the Cells to the last Name Fred then
once found Highlite 7 colums to the right, so for example the last Fred may
be found in Cell A 47, so in this intance the highlighted cells would be A2:
G47 Thankyou.

Option Explicit
Sub myFind()

Dim FoundCell As Range
With ActiveSheet
With .Range("a:a")
Set FoundCell = .Cells.Find(what:="Fred", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox "Fred wasn't found!"
Else
Application.Goto FoundCell, scroll:=True
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Once you locate FoundCell, you should be able to run this line of code...

Worksheets("Sheet1").Range("A2:G" & FoundCell.Row).Select

in order to select the range you indicated.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Sorry, test code accidentally left in... for your stated condition, the
statement should be...

ActiveSheet.Range("A2:G" & FoundCell.Row).Select

unless you extend the End With statement for the With ActiveSheet block to
encompass the statement, in which case you can leave the ActiveSheet off...

..Range("A2:G" & FoundCell.Row).Select

Rick

 

[Steved]

Hello from Steved

Thankyou

I put in your Script as show'n below it finds in this case Panmure but does
not select ( highlite ) the cells, please what have I've done wrong.

Thankyou.

Option Explicit
Sub myFind()

Dim FoundCell As Range
With ActiveSheet
With .Range("a:a")
Set FoundCell = .Cells.Find(what:="Panmure", _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
ActiveSheet.Range("A2:G" & FoundCell.Row).Select
Else
Application.Goto FoundCell, scroll:=True
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

It looks like you put it in the wrong IF section... If FoundCell Is Nothing
Then means FoundCell has nothing in it... try putting it in the Else section
(and put whatever was originally in the first section back).

Rick

 

[Steved]

Hello from Steved

Thankyou it does as you have instructed

Ok is it possible using excell 2007 do a Data Sort please on Col E

Thanks for yor time on my issue

 

[Rick Rothstein \(MVP - VB\)]

Ok is it possible using excell 2007 do a Data Sort please on Col E

Yes, but I am not sure why you would be having trouble with this. Highlight
your range, click on the Data tab and find the Sort icon in the toolbar that
is displayed (looks like a 4-cell table with A's and Z's in it.

Rick