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

On Mon, 4 Jul 2005 07:34:01 -0700, "Angus"
wrote:

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:

I get this quite frequently.

In the VBA window, go to Tools References. In the check list of
Libraries and Applications you'll find one which is probably ticked
(at the top) but described as 'Missing'. If you find one, look for a
different (earlier) version in the list and check this instead.

For instance it might say "Missing - Microsoft Excel 10.0 Object
Library", in which case find version 9.0 of the Excel Object Library.

HTH



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





__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________