Delete all row data EXCEPT
 

I have imported data from an Outlook file that leaves me with an excel
spreadsheet with 2 columns. Data in the columns include groups that I must
keep in the following format:

In column A: I have 8 fields named
txtBusiness,txtName,txtAddress,txtCity,selState,tx tZip,txtEmail,and txtPhone

In column B, I have the actual data. However, I also have a lot of other
garbage in column A and B that I do not need and would like to delete with a
macro. I have searched the knowledge base high and low and have found
nothing that seem to fit this application. I simply need to save any row
that has the txtBusiness through txtPhone in column A and the corresponding
data in column B, and delete everything else. Thanks in advance

Delete all row data EXCEPT
 

Dim r as Range
Dim i as Long
for i = cells(rows.count,1).End(xlup).Row to 1 step - 1
set r = Cells(i,1)
if instr(r,"txtName") + instr(r,"txtAddress") + instr(r,"txtBusiness") _
+ instr(r,"txtCity") + instr(r,"selState") + instr(r,"txtZip") + _
instr(r,"txtPhone") = 0 then
r.EntireRow.Delete
end if
Next

--
Regards,
Tom Ogilvy


"kcoppoletta" wrote in message
...
I have imported data from an Outlook file that leaves me with an excel
spreadsheet with 2 columns. Data in the columns include groups that I must
keep in the following format:

In column A: I have 8 fields named
txtBusiness,txtName,txtAddress,txtCity,selState,tx tZip,txtEmail,and
txtPhone

In column B, I have the actual data. However, I also have a lot of other
garbage in column A and B that I do not need and would like to delete with
a
macro. I have searched the knowledge base high and low and have found
nothing that seem to fit this application. I simply need to save any row
that has the txtBusiness through txtPhone in column A and the
corresponding
data in column B, and delete everything else. Thanks in advance

Delete all row data EXCEPT
 

I would bet you could just use autofilter for values in column A which "does
not begin with" txt, and delete the rows?

"kcoppoletta" wrote in message
...
I have imported data from an Outlook file that leaves me with an excel
spreadsheet with 2 columns. Data in the columns include groups that I must
keep in the following format:

In column A: I have 8 fields named
txtBusiness,txtName,txtAddress,txtCity,selState,tx tZip,txtEmail,and
txtPhone

In column B, I have the actual data. However, I also have a lot of other
garbage in column A and B that I do not need and would like to delete with
a
macro. I have searched the knowledge base high and low and have found
nothing that seem to fit this application. I simply need to save any row
that has the txtBusiness through txtPhone in column A and the
corresponding
data in column B, and delete everything else. Thanks in advance

Delete all row data EXCEPT
 

I missed selState, so since custom autofilter gives you up to two criteria,
they should be

"does not begin with " txt

AND

"does not equal" selState


"William Benson" wrote in message
...
I would bet you could just use autofilter for values in column A which
"does not begin with" txt, and delete the rows?

"kcoppoletta" wrote in message
...
I have imported data from an Outlook file that leaves me with an excel
spreadsheet with 2 columns. Data in the columns include groups that I
must
keep in the following format:

In column A: I have 8 fields named
txtBusiness,txtName,txtAddress,txtCity,selState,tx tZip,txtEmail,and
txtPhone

In column B, I have the actual data. However, I also have a lot of other
garbage in column A and B that I do not need and would like to delete
with a
macro. I have searched the knowledge base high and low and have found
nothing that seem to fit this application. I simply need to save any
row
that has the txtBusiness through txtPhone in column A and the
corresponding
data in column B, and delete everything else. Thanks in advance