Select column by finding the text

Hello There,

I want to find a text in a sheet and then I want to select all the data in
that particular column. (note : In between the data blank cells are there.)

Thanks & Regards,

Sreenivas

Here is some code for you to try. This code looks for the Word "This"
anywhere on Sheet1 and selects all of the populated cells in that column...

Sub SelectColumn()
Dim rngFound As Range
Dim rngToSearch As Range
Dim rngFinal As Range
Dim wks As Worksheet

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells
Set rngFound = rngToSearch.Find(What:="This", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)

If rngFound Is Nothing Then
MsgBox "Sorry, Not found."
Else
Set rngToSearch = rngFound.EntireColumn
Set rngFound = rngToSearch.SpecialCells(xlCellTypeFormulas)
If Not rngFound Is Nothing Then
Set rngFinal = rngFound
End If
Set rngFound = rngToSearch.SpecialCells(xlCellTypeConstants)
If Not rngFound Is Nothing Then
Set rngFinal = Union(rngFound, rngFinal)
End If
rngFinal.Select
End If
End Sub
--
HTH...

Jim Thomlinson


"cheenu" wrote:

> Hello There,
>
> I want to find a text in a sheet and then I want to select all the data in
> that particular column. (note : In between the data blank cells are there.)
>
> Thanks & Regards,
>
> Sreenivas