[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel find all if found select the whole row.

Posted on 2007-12-02
11
Medium Priority
?
1,346 Views
Last Modified: 2012-08-14
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
Comment
Question by:bsharath
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20391330
Assuming 2007 is as 2003 then when you find your datun use searchResult.entirerow.select

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20391336
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
 
LVL 11

Author Comment

by:bsharath
ID: 20391356
Chris thanks for the response...
>>use searchResult.entirerow.select
Can you tell me where the option would be
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 25

Expert Comment

by:imitchie
ID: 20391953
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20392203
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
 
LVL 11

Author Comment

by:bsharath
ID: 20393442
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20393961
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
 
LVL 11

Author Comment

by:bsharath
ID: 20393969
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20410418
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
 
LVL 11

Author Comment

by:bsharath
ID: 20410450
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 20410537
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question