Filter data

Steffmeister

Board Regular
Joined
Nov 7, 2005
Messages
195
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Steffmeister

Board Regular
Joined
Nov 7, 2005
Messages
195
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?

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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Steffmeister said:
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 ...
Code:
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

Board Regular
Joined
Nov 7, 2005
Messages
195

ADVERTISEMENT

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

Board Regular
Joined
Nov 7, 2005
Messages
195
I got this now:

Code:
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

Steffmeister said:
....
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

Board Regular
Joined
Nov 7, 2005
Messages
195
Yogi Anand said:
Steffmeister said:
....
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

Board Regular
Joined
Nov 7, 2005
Messages
195
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

Board Regular
Joined
Nov 7, 2005
Messages
195
OK I got the next macro:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top