Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!

see if this does what you want. Suggest that you make back-up of your data
before testing.

Sub DeleteData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim Lr As Long
Dim FoundCell As Range
Dim Search As String
Dim icount As Long

With ThisWorkbook

Set ws1 = .Worksheets("untitled")
Set ws2 = .Worksheets("bluecard_homeplanaid")

End With

'assume you have a header in row 1
StartRow = 2

icount = 0

With ws1

EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lr = StartRow To EndRow

Search = .Cells(Lr, 2).Value

If Search < "" Then

Set FoundCell = ws2.Columns(2).Find(Search, _
After:=ws2.Cells(1, 2), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing = False Then

FoundCell.EntireRow.Delete

icount = icount + 1

End If

End If

Next

End With

msg = MsgBox(icount & " Records Deleted", vbInformation, "Delete Data")

End Sub

--
jb


"Peruanos72" wrote:

Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!

Hi John,

I'm getting a "Subscript out of range" error when the code hits
Set ws1 = .Worksheets("untitled")

i moved that worksheet so it's the first worksheet but the error still
comes up. I only have the two worksheets in the workbook.

Thoughts??

"john" wrote:

see if this does what you want. Suggest that you make back-up of your data
before testing.

Sub DeleteData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim Lr As Long
Dim FoundCell As Range
Dim Search As String
Dim icount As Long

With ThisWorkbook

Set ws1 = .Worksheets("untitled")
Set ws2 = .Worksheets("bluecard_homeplanaid")

End With

'assume you have a header in row 1
StartRow = 2

icount = 0

With ws1

EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lr = StartRow To EndRow

Search = .Cells(Lr, 2).Value

If Search < "" Then

Set FoundCell = ws2.Columns(2).Find(Search, _
After:=ws2.Cells(1, 2), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing = False Then

FoundCell.EntireRow.Delete

icount = icount + 1

End If

End If

Next

End With

msg = MsgBox(icount & " Records Deleted", vbInformation, "Delete Data")

End Sub

--
jb


"Peruanos72" wrote:

Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!

It means it can't find a worksheet with that name - I copied the names from
your post - check the spellings & try again.
--
jb


"Peruanos72" wrote:

Hi John,

I'm getting a "Subscript out of range" error when the code hits
Set ws1 = .Worksheets("untitled")

i moved that worksheet so it's the first worksheet but the error still
comes up. I only have the two worksheets in the workbook.

Thoughts??

"john" wrote:

see if this does what you want. Suggest that you make back-up of your data
before testing.

Sub DeleteData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim Lr As Long
Dim FoundCell As Range
Dim Search As String
Dim icount As Long

With ThisWorkbook

Set ws1 = .Worksheets("untitled")
Set ws2 = .Worksheets("bluecard_homeplanaid")

End With

'assume you have a header in row 1
StartRow = 2

icount = 0

With ws1

EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lr = StartRow To EndRow

Search = .Cells(Lr, 2).Value

If Search < "" Then

Set FoundCell = ws2.Columns(2).Find(Search, _
After:=ws2.Cells(1, 2), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing = False Then

FoundCell.EntireRow.Delete

icount = icount + 1

End If

End If

Next

End With

msg = MsgBox(icount & " Records Deleted", vbInformation, "Delete Data")

End Sub

--
jb


"Peruanos72" wrote:

Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!

The spelling was correct. my code copies my tabs to a new workbook so the
master file is not touched and my code runs in the new workbook from the
master file. it appears your code doesn't work in the new book that's created
but it does work if done in the master file.

Is there a way to run your code in my new workbook?

"john" wrote:

It means it can't find a worksheet with that name - I copied the names from
your post - check the spellings & try again.
--
jb


"Peruanos72" wrote:

Hi John,

I'm getting a "Subscript out of range" error when the code hits
Set ws1 = .Worksheets("untitled")

i moved that worksheet so it's the first worksheet but the error still
comes up. I only have the two worksheets in the workbook.

Thoughts??

"john" wrote:

see if this does what you want. Suggest that you make back-up of your data
before testing.

Sub DeleteData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim Lr As Long
Dim FoundCell As Range
Dim Search As String
Dim icount As Long

With ThisWorkbook

Set ws1 = .Worksheets("untitled")
Set ws2 = .Worksheets("bluecard_homeplanaid")

End With

'assume you have a header in row 1
StartRow = 2

icount = 0

With ws1

EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lr = StartRow To EndRow

Search = .Cells(Lr, 2).Value

If Search < "" Then

Set FoundCell = ws2.Columns(2).Find(Search, _
After:=ws2.Cells(1, 2), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing = False Then

FoundCell.EntireRow.Delete

icount = icount + 1

End If

End If

Next

End With

msg = MsgBox(icount & " Records Deleted", vbInformation, "Delete Data")

End Sub

--
jb


"Peruanos72" wrote:

Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!

did not see that requirement in your original post.
Yes is the short answer but it would help if you post all the code you are
using.

I am about to leave office & out for evening will respond asap
--
jb


"Peruanos72" wrote:

The spelling was correct. my code copies my tabs to a new workbook so the
master file is not touched and my code runs in the new workbook from the
master file. it appears your code doesn't work in the new book that's created
but it does work if done in the master file.

Is there a way to run your code in my new workbook?

"john" wrote:

It means it can't find a worksheet with that name - I copied the names from
your post - check the spellings & try again.
--
jb


"Peruanos72" wrote:

Hi John,

I'm getting a "Subscript out of range" error when the code hits
Set ws1 = .Worksheets("untitled")

i moved that worksheet so it's the first worksheet but the error still
comes up. I only have the two worksheets in the workbook.

Thoughts??

"john" wrote:

see if this does what you want. Suggest that you make back-up of your data
before testing.

Sub DeleteData()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim StartRow As Long
Dim EndRow As Long
Dim Lr As Long
Dim FoundCell As Range
Dim Search As String
Dim icount As Long

With ThisWorkbook

Set ws1 = .Worksheets("untitled")
Set ws2 = .Worksheets("bluecard_homeplanaid")

End With

'assume you have a header in row 1
StartRow = 2

icount = 0

With ws1

EndRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For Lr = StartRow To EndRow

Search = .Cells(Lr, 2).Value

If Search < "" Then

Set FoundCell = ws2.Columns(2).Find(Search, _
After:=ws2.Cells(1, 2), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCell Is Nothing = False Then

FoundCell.EntireRow.Delete

icount = icount + 1

End If

End If

Next

End With

msg = MsgBox(icount & " Records Deleted", vbInformation, "Delete Data")

End Sub

--
jb


"Peruanos72" wrote:

Hello all,

I have a tab named "untitled" and a tab named "bluecard_homeplanaid".
I have data in tab "untitled" column "B". They're numbers such as
200906180333.
I need code to that takes the number in the first cell "B1" in tab
"untitled" and searches column "B" in tab "bluecard_homeplanaid". If found,
then that row in tab "bluecard_homeplanaid" would be deleted. This process
repeats for all of the numbers in column "B" in tab "untitled.

Note: The amount of data in column "B" , tab "untitled" changes daily.

thanks in advance!!!