Filter data

[Steffmeister]

Hi guys,

I have data in Excel and want to filter cells which contains 'dyn'.
The cells with the name dyn in it must copied in sheet 2. In sheet1 must those addresses deleted. Must this be done with a macro?
Can somebody help me?

Tnx for now

Steffmeister

 

[Steffmeister]

Hi guys,

I've found a macro which not work correctly. The macro will search a name 'dyn' and move them from Sheet1 A1 to a new Sheet, but I want that he copy it to the existing sheet2.
And the macro is not correct because he always copy the first cell A1 and don't copy the first cell with the word 'dyn' in it.
Can somebody help me with this code?

Sub Filter()
Dim Src As String
Src = ActiveSheet.Name

For Each SrchString In Array("dyn")
    

    'MAKE COPY OF ALL ROWS WITH STRING IN COL B
    CopyByCriteria ("=FIND(" & Chr(34) & SrchString & Chr(34) & ",A1)>0")
    
    Sheets(Src).Activate
    
    'MAKE COPY OF ALL ROWS WITH "REMOTE" IN COL B
    CopyByCriteria ("=iserror(FIND(" & Chr(34) & SrchString & Chr(34) & ",A1)>0)")
    
    'MAKE NEW COPY THE SOURCE SHEET
    Application.DisplayAlerts = False
    Sheets(Src).Delete
    ActiveSheet.Name = Src
    Application.DisplayAlerts = True
    
Next SrchString

End Sub


Sub CopyByCriteria(Crit)
Dim SrcRange As Range
Dim CritRange As Range

    Set SrcRange = Range("A1:B65536")
    Set CritRange = Range("IV1:IV2")
    
    'Establish Criteria
    CritRange(1).ClearContents
    CritRange(2).Formula = Crit
    
    'Create Target Sheet
    Sheets.Add after:=ActiveSheet
    
    'Use Advanced Filter to copy data that meets criteria
    SrcRange.AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=CritRange, _
    CopyToRange:=Range("A1"), Unique:=False
    
    ' Clear Criteria
    CritRange.ClearContents
End Sub

Grts. Steffmeister

 

[Steffmeister]

Does somebody knows a solution for me plz?

 

[Yogi Anand]

Hi guys,

I have data in Excel and want to filter cells which contains 'dyn'.
The cells with the name dyn in it must copied in sheet 2. In sheet1 must those addresses deleted. Must this be done with a macro?
Can somebody help me?

Tnx for now

Steffmeister

Hi Steffmeister:

Without using a macro, you can do it using the Filter command and copy command. Using a macro however will automate the process ... let us try the following ...

Sub ySteff()
'
' Data to be filtered is in Sheet1 ... cells A1 down

    Sheets("sheet2").Activate
    
'incorporating Filter Criterion

    Sheets("sheet2").Range("c2").Formula = "=ISERROR(FIND(""dyn"",A2))"
    
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("C1:C2"), CopyToRange:=Sheets("Sheet2").Range("A5"), Unique:=False
    
    'data without dyn's will be copied to column C of Sheet1 (it can of course be copied to column A ...
    'I want to retain the original data ... just in case
    
    Sheets("sheet2").Range("a5", Range("a5").End(xlDown)).Copy [sheet1!C1]

End Sub

 

[Steffmeister]

Hi Yogi Anand,

Tnx for the reply. I tried the macro, but the data which NOT contains 'dyn' will be copied to sheet 2, but is it possible that the data what contains 'dyn' to be copied to sheet 2?

Tnx, grts Steffmeister

 

[Steffmeister]

I got this now:

Sub ySteff()
'
' Data to be filtered is in Sheet1 ... cells A1 down

    Sheets("sheet2").Activate
    
'incorporating Filter Criterion

    Sheets("sheet2").Range("c2").Formula = "=(FIND(""dyn"",A2))"
    
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("C1:C2"), CopyToRange:=Sheets("Sheet2").Range("A5"), Unique:=False
    
    'data without dyn's will be copied to column C of Sheet1 (it can of course be copied to column A ...
    'I want to retain the original data ... just in case
    
    Sheets("sheet2").Range("a5", Range("a5").End(xlDown)).Copy [sheet1!C1]

End Sub

But he alway get the first record (A1). Why does he do that?
And is it posible to delete the clear cells out of sheet2 column A?

Grts. Steffmeister

 

[Yogi Anand]

....
But he alway get the first record (A1). Why does he do that?
And is it posible to delete the clear cells out of sheet2 column A?

Grts. Steffmeister

Hi Steffmister:

Your questions are not clear to me ... please explain a bit clearly ... and then let us take it from there!

 

[Steffmeister]

....
But he alway get the first record (A1). Why does he do that?
And is it posible to delete the clear cells out of sheet2 column A?

Grts. Steffmeister

Hi Steffmister:

Your questions are not clear to me ... please explain a bit clearly ... and then let us take it from there!

Hi Yogi Anand,

Sorry for the bad explanation.
If I run the macro he must only get the cells which have the word 'dyn' in it, but he also get the first cell (A1) which haven't the word 'dyn' in it.

Second question is, het put all the records in sheet 2, but with open cells (cells which not contain data). I'd like that he put the data under each cell.

I hope this is clear enough, else I try to make a printscreen.

Grts. Steffmeister

 

[Steffmeister]

Got another question, if i want to filter 2 words (e.g. 'dyn' and 'hursley') how can I do that?
And is it possible to delete the data what is copied in sheet 2?

 

[Steffmeister]

OK I got the next macro:

Sub Filter()
'
' Data to be filtered is in Sheet1 ... cells A1 down

    Sheets("sheet2").Activate
    
'incorporating Filter Criterion

    Sheets("sheet2").Range("B2").Formula = "=(FIND(""dyn"",A2))"
   
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("B1:B2"), CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=False
    
'data with dyn's will be copied to column C of Sheet1 (it can of course be copied to column A ...
'I want to retain the original data ... just in case
    
    Sheets("sheet2").Range("a1", Range("a1").End(xlDown)).Copy [sheet1!B1]

' Data to be filtered is in Sheet1 ... cells A1 down

    Sheets("sheet2").Activate

'incorporating Filter Criterion
    Sheets("sheet2").Range("C2").Formula = "=(FIND(""Non-existent"",A2))"
    
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("C1:C2"), CopyToRange:=Sheets("Sheet2").Range("B1"), Unique:=False
        
'data with Non-existent will be copied to column C of Sheet1 (it can of course be copied to column A ...
'I want to retain the original data ... just in case
    Sheets("sheet2").Range("B1", Range("B1").End(xlDown)).Copy [sheet1!C1]
        
' Data to be filtered is in Sheet1 ... cells A1 down

    Sheets("sheet2").Activate
        
'incorporating Filter Criterion
    Sheets("sheet2").Range("D2").Formula = "=(FIND(""hursley"",A2))"
    
    Sheets("Sheet1").Range("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("D1:D2"), CopyToRange:=Sheets("Sheet2").Range("C1"), Unique:=False

'data with Non-existent will be copied to column C of Sheet1 (it can of course be copied to column A ...
'I want to retain the original data ... just in case
    Sheets("sheet2").Range("D1", Range("D1").End(xlDown)).Copy [sheet1!D1]

End Sub

He put all the words with 'dyn', 'non existent' and 'hursley' into sheet 2 column A, B and C.
Now I want that he delete the cells which stands in sheet 2 from Sheet 1. Only I don't know how? Can somebody help me plz?

Tnx Steffmeister