Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1350
  • Last Modified:

Excel find all if found select the whole row.

Hi,

There are times when i need to Find a word or Find all words.Is there a way i can select the whole row in the sheet when found and move.Is there a inbuit option in excel 2007.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 6
  • 4
1 Solution
 
Chris BottomleyCommented:
Assuming 2007 is as 2003 then when you find your datun use searchResult.entirerow.select

Chris
0
 
Chris BottomleyCommented:
Sorry

Keyboard trouble ... as far as I know we should not be using proposed solution and I certainly didn't mean to select it.

Cris
0
 
bsharathAuthor Commented:
Chris thanks for the response...
>>use searchResult.entirerow.select
Can you tell me where the option would be
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
imitchieCommented:
In Macro, after you find, you can use the VBA code like the following

Dim r as Range
r = Range("A2:B10")
r = r.Find(...)
r.EntireRow.Cut
.. paste..
0
 
Chris BottomleyCommented:
Dim sRange As Range
'
    Set sRange = Cells.Find(What:="fred", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    srange.EntireRow.Select

Chris
0
 
bsharathAuthor Commented:
First i need to find then ?
I created a new module and tried both Macro but nothing happened any thing that i am doing wrong...
0
 
Chris BottomleyCommented:
Okay

Apologies I assumed too much.

You want a process that will find a string and when found will move it.

How do you want to call the process and what kind of movement is involved ... end of sheet new sheet or whatever?

Chris
0
 
bsharathAuthor Commented:
Chris.
I want to find all and then when selected the data found it only selects the cell that was in the search criteria.What i want is when found i should be able to select the whole row and move them to new sheet.
0
 
Chris BottomleyCommented:
APologies but can I ask:

You want to find all rows which have the search datum somewhere in the row such that only the cells with the datum are selected but to be able to expand the selection so that each of these row(s) will be selected if and only if 'requested' by the user.

Is this a correct understanding?

Chris
0
 
bsharathAuthor Commented:
Chris i shall give you an example.
Say i want to search for "Sharath" in the excel sheet

Colum A                        Colum   B
Sharath                             Reddy

So when i search "Sharath" the colum A data is highlighted and i can copy the cell to another place.
Now what i want is when i search for "Sharath" i shouls be able to select the whole row.

So " sharath  Reddy " both the cells have to be copied.

This would be useful when i search for a data which is available in 100's of rows.I should be able to select the whole row.
0
 
Chris BottomleyCommented:
If you want to do this row by row then to expand the range to whole row simply click the row number at the far left.

If you want to do this for all rows with (sharath) then this will require a simple macro .

1. Find : find all : and ctrl + a to select ... at this point you have the required cells of data selected.
2. If you require rows selecting then call rowsel
3. Copy and paste to your destination.

Chris


Sub rowsel()
    Selection.EntireRow.Select
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now