AdvancedFilter VBA Adjustment

[mpb1]

Good evening All
I am hoping that someone will be able to help with this.
I have some small VBA code (shown below), which applies to the following example of a data set:

Worksheet "Invoice Record"
A B C
1 Header Header Header
2 M100 152.34 03
3 M101 100.02 NP
4 M100 250.65 02
5 M100 565.52 01
6 M102 745.87 NP
7 M101 985.65 03

Column A is Formatted Text, B Number, C Text. (Though columns A & C are flexible to other formats)

The code describes:- from column A copy unique values only to another location (another Worksheet ("General Report") in this case)
This works well, however I would really like the code to look at only the cells in column A that do not have NP as it's corresponding entry in Column C.
ie to ignore rows with NP in column C

I do hope that this is an easy fix, I am a novice at VBA but am learning! (Especially with help from this group)
Here Is the Code:

Sub ECRGeneralReportPopulation()
' Automates The General Report Population

Application.Interactive = False
Dim myRng As Range
Sheets("General Report").Range("A:A").ClearContents
With Sheets("Invoice Record")
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With
myRng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("General Report").Range("A2"), Unique:=True
Application.Interactive = True
End Sub

I hope someone can help
Cheers
Mathew

 

[Dave Peterson]

You have a couple responses to your others posts.

mpb1 wrote:

Good evening All
I am hoping that someone will be able to help with this.
I have some small VBA code (shown below), which applies to the following
example of a data set:

Worksheet "Invoice Record"
A B C
1 Header Header Header
2 M100 152.34 03
3 M101 100.02 NP
4 M100 250.65 02
5 M100 565.52 01
6 M102 745.87 NP
7 M101 985.65 03

Column A is Formatted Text, B Number, C Text. (Though columns A & C are
flexible to other formats)

The code describes:- from column A copy unique values only to another
location (another Worksheet ("General Report") in this case)
This works well, however I would really like the code to look at only the
cells in column A that do not have NP as it's corresponding entry in Column C.
ie to ignore rows with NP in column C

I do hope that this is an easy fix, I am a novice at VBA but am learning!
(Especially with help from this group)
Here Is the Code:

Sub ECRGeneralReportPopulation()
' Automates The General Report Population

Application.Interactive = False
Dim myRng As Range
Sheets("General Report").Range("A:A").ClearContents
With Sheets("Invoice Record")
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With
myRng.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheets("General Report").Range("A2"), Unique:=True
Application.Interactive = True
End Sub

I hope someone can help
Cheers
Mathew