I need a macro to find the numbers in column B of sheet "Backup", then look
at column B of "Raw data", if not found the same number delete entire row.
Like using vlookup if return #N/A then delete row, how to do so?

In my opinion this could be a good way:
write a code that will
1/ go thru column B of sheet Backup
2/ try to find each value in column B of Raw Data and if not succes
then
3/ delete entire row in Backup sheet
or ask someone who can write it for you.

Hi,

Try this (test first!):

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range

Set rngA = Worksheets("Backup").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)
Set rngB = Worksheets("Raw Data").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)

' In RngA but not in RngB

For i = rngA.Count To 1 Step -1 ' Loop through Backup numbers
If IsError(Application.Match(rngA(i).Value, rngB, 0)) Then
rngA(i).EntireRow.Delete ' Delete from Backup if not found in Raw Data
End If
Next

End Sub



"Angus" wrote:

I need a macro to find the numbers in column B of sheet "Backup", then look
at column B of "Raw data", if not found the same number delete entire row.
Like using vlookup if return #N/A then delete row, how to do so?

Just a word of warning...

The unqualified cells(rows.count,"B") will refer to the activesheet. I think
I'd qualify those ranges.

with worksheets("backup")
Set rngA = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

with worksheets("raw data")
Set rngB = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

Another variation:

with worksheets("backup")
Set rngA = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with

with worksheets("raw data")
Set rngb = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with



Toppers wrote:

Hi,

Try this (test first!):

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range

Set rngA = Worksheets("Backup").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)
Set rngB = Worksheets("Raw Data").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)

' In RngA but not in RngB

For i = rngA.Count To 1 Step -1 ' Loop through Backup numbers
If IsError(Application.Match(rngA(i).Value, rngB, 0)) Then
rngA(i).EntireRow.Delete ' Delete from Backup if not found in Raw Data
End If
Next

End Sub

"Angus" wrote:

I need a macro to find the numbers in column B of sheet "Backup", then look
at column B of "Raw data", if not found the same number delete entire row.
Like using vlookup if return #N/A then delete row, how to do so?

--

Dave Peterson

Dave,
Thanks: I have fallen foul before of the error in not qualifying
the ranges (so I should know better!).


"Dave Peterson" wrote:

Just a word of warning...

The unqualified cells(rows.count,"B") will refer to the activesheet. I think
I'd qualify those ranges.

with worksheets("backup")
Set rngA = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

with worksheets("raw data")
Set rngB = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

Another variation:

with worksheets("backup")
Set rngA = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with

with worksheets("raw data")
Set rngb = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with



Toppers wrote:

Hi,

Try this (test first!):

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range

Set rngA = Worksheets("Backup").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)
Set rngB = Worksheets("Raw Data").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)

' In RngA but not in RngB

For i = rngA.Count To 1 Step -1 ' Loop through Backup numbers
If IsError(Application.Match(rngA(i).Value, rngB, 0)) Then
rngA(i).EntireRow.Delete ' Delete from Backup if not found in Raw Data
End If
Next

End Sub

"Angus" wrote:

I need a macro to find the numbers in column B of sheet "Backup", then look
at column B of "Raw data", if not found the same number delete entire row.
Like using vlookup if return #N/A then delete row, how to do so?

--

Dave Peterson

It's perfect, thanks.

"Toppers" wrote:

Dave,
Thanks: I have fallen foul before of the error in not qualifying
the ranges (so I should know better!).


"Dave Peterson" wrote:

Just a word of warning...

The unqualified cells(rows.count,"B") will refer to the activesheet. I think
I'd qualify those ranges.

with worksheets("backup")
Set rngA = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

with worksheets("raw data")
Set rngB = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

Another variation:

with worksheets("backup")
Set rngA = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with

with worksheets("raw data")
Set rngb = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with



Toppers wrote:

Hi,

Try this (test first!):

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range

Set rngA = Worksheets("Backup").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)
Set rngB = Worksheets("Raw Data").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)

' In RngA but not in RngB

For i = rngA.Count To 1 Step -1 ' Loop through Backup numbers
If IsError(Application.Match(rngA(i).Value, rngB, 0)) Then
rngA(i).EntireRow.Delete ' Delete from Backup if not found in Raw Data
End If
Next

End Sub

"Angus" wrote:

I need a macro to find the numbers in column B of sheet "Backup", then look
at column B of "Raw data", if not found the same number delete entire row.
Like using vlookup if return #N/A then delete row, how to do so?

--

Dave Peterson

Dear experts,

it works perfect on my laptop (office 2003), but when i copy to my desktop
(office 2002), it comes with a error message "Compile error: Can't find
project or library" and highlight following line:

For i = rngA.Count To 1 Step -1 ' Loop through Backup numbers

What's wrong?

"Angus" wrote:

It's perfect, thanks.

"Toppers" wrote:

Dave,
Thanks: I have fallen foul before of the error in not qualifying
the ranges (so I should know better!).


"Dave Peterson" wrote:

Just a word of warning...

The unqualified cells(rows.count,"B") will refer to the activesheet. I think
I'd qualify those ranges.

with worksheets("backup")
Set rngA = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

with worksheets("raw data")
Set rngB = .Range("B1:b" & .Cells(.Rows.Count, "B").End(xlUp).Row)
end with

Another variation:

with worksheets("backup")
Set rngA = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with

with worksheets("raw data")
Set rngb = .Range("B1", .Cells(.Rows.Count, "B").end(xlup))
end with



Toppers wrote:

Hi,

Try this (test first!):

Sub CompareTwoColumns()

Dim rngA As Range
Dim rngB As Range

Set rngA = Worksheets("Backup").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)
Set rngB = Worksheets("Raw Data").Range("B1:b" & Cells(Rows.Count,
"B").End(xlUp).Row)

' In RngA but not in RngB

For i = rngA.Count To 1 Step -1 ' Loop through Backup numbers
If IsError(Application.Match(rngA(i).Value, rngB, 0)) Then
rngA(i).EntireRow.Delete ' Delete from Backup if not found in Raw Data
End If
Next

End Sub

"Angus" wrote:

I need a macro to find the numbers in column B of sheet "Backup", then look
at column B of "Raw data", if not found the same number delete entire row.
Like using vlookup if return #N/A then delete row, how to do so?

--

Dave Peterson