Lopp in column to find and select

[al007]

Looking for a code which would loop in a column to find and select what
is in my active cell.

Eg: Activecell : B5 = "Test"

B6 & B7 also contains "Test"

How can I loop in column B to find & select all 3 cells.

Thxs

[Norman Jones]

Hi Al007,

Try:

'=============>>
Public Sub Tester1A()
Dim rng As Range
Dim rngFound As Range
Dim rngOut As Range
Dim sStr As String
Dim firstAdd As String

Set rng = ActiveCell
sStr = rng.Value

Set rngFound = rng.EntireColumn.Find _
(What:=sStr, _
After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext)

If Not rngFound Is Nothing Then
firstAdd = rngFound.Address
Set rngOut = rngFound
End If

Do
Set rngFound = rng.EntireColumn.FindNext(rngFound)
If Not rngFound Is Nothing Then
Set rngOut = Union(rngOut, rngFound)
End If
Loop While Not rngFound Is Nothing _
And rngFound.Address <> firstAdd


If Not rngOut Is Nothing Then rngOut.Select

End Sub
'<<=============

This is an adaptation of the example code provided by VBA help.


---
Regards,
Norman


"al007" <[email protected]> wrote in message
news:[email protected]...
> Looking for a code which would loop in a column to find and select what
> is in my active cell.
>
> Eg: Activecell : B5 = "Test"
>
> B6 & B7 also contains "Test"
>
> How can I loop in column B to find & select all 3 cells.
>
> Thxs
>

[al007]

thxs a lot for your help !!

[aspiringlawtechie0615]

While i am deciphering the code below, may i know if you also could help
with my scenario.

Given: A robust worksheet with almost 10,000 rows.

Column A could have the same entries.
ig A1= Apple; A2= Apple
If this is the case, then the row of A2 must be deleted.

We are aiming to have a file whose Column A entries are unique from each
other.


Please help...
Thank you for your immediate consideration.

Happy New Year!


"Norman Jones" wrote:

> Hi Al007,
>
> Try:
>
> '=============>>
> Public Sub Tester1A()
> Dim rng As Range
> Dim rngFound As Range
> Dim rngOut As Range
> Dim sStr As String
> Dim firstAdd As String
>
> Set rng = ActiveCell
> sStr = rng.Value
>
> Set rngFound = rng.EntireColumn.Find _
> (What:=sStr, _
> After:=rng(1), _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlNext)
>
> If Not rngFound Is Nothing Then
> firstAdd = rngFound.Address
> Set rngOut = rngFound
> End If
>
> Do
> Set rngFound = rng.EntireColumn.FindNext(rngFound)
> If Not rngFound Is Nothing Then
> Set rngOut = Union(rngOut, rngFound)
> End If
> Loop While Not rngFound Is Nothing _
> And rngFound.Address <> firstAdd
>
>
> If Not rngOut Is Nothing Then rngOut.Select
>
> End Sub
> '<<=============
>
> This is an adaptation of the example code provided by VBA help.
>
>
> ---
> Regards,
> Norman
>
>
> "al007" <[email protected]> wrote in message
> news:[email protected]...
> > Looking for a code which would loop in a column to find and select what
> > is in my active cell.
> >
> > Eg: Activecell : B5 = "Test"
> >
> > B6 & B7 also contains "Test"
> >
> > How can I loop in column B to find & select all 3 cells.
> >
> > Thxs
> >
>
>
>