selecting specific text and extract information

[max.bizarre]

Hi,

I just started using VBA excel. I want to select a column with specific text and then get all the non-contiguous cells with information. I want to copy this information and present it in a different worksheet in the order in which it was taken. I cant seem to figure out how to do that.

Thanx in advance.
M

 

[Misca]

This piece of code finds the values you're looking for from the given range and copies the found rows to another location:

Sub FindAndCopy()
Dim Find_Item As String
Dim Rng As Range
Dim Found As Range
Dim c As Range
Find_Item = "0"     'What to look
Set Rng = ActiveSheet.Range("A:A")  'Where to look from
Set c = Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)    'Where to put the results
    If c.Row = 2 Then Set c = c.Offset(-1, 0)   'if sheet empty
Set Found = Union(ActiveSheet.Cells(1, 1), Find_Range(Find_Item, Rng))
'Found can not be nothing so:
Found.EntireRow.Copy c

End Sub
Function Find_Range(Find_Item As Variant, _
    Search_Range As Range, _
    Optional LookIn As Variant, _
    Optional LookAt As Variant, _
    Optional MatchCase As Boolean) As Range
     
    Dim c As Range
    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False
     
    With Search_Range
        Set c = .Find( _
        What:=Find_Item, _
        LookIn:=LookIn, _
        LookAt:=LookAt, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=MatchCase, _
        SearchFormat:=False)
        If Not c Is Nothing Then
            Set Find_Range = c
            firstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
     
End Function

The macro uses the "Kick Butt VBA Find"-function found from http://www.ozgrid.com/forum/showthread.php?t=27240&page=1

 

[pgc01]

Hi Max
Welcome to the board

Another option is to use autofilter and filter based on the value you want. Then you just have to copy the visible rows. If you have doubts with the syntax, you can start by recording a macro while doing it manually on the worksheet.

 

[max.bizarre]

Thank you Misca and pgc01. I tried the autofilter method and it worked perfectly. Took help of the record macro option from the excel sheet to see how it works. Couldn't have done without you guys. Thanx a lot.