Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2006

Tip: Looking for answers? Try searching our database.

Sorting

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
bodhisatvaofboogie - 24 May 2006 19:34 GMT
I am trying to sort for some pretty specific stuff and delete everything
else.  

Sort Column A for the letters Whse: ##  and Column B for the letters Source:
###
the ###'s vary but I want everything with those letters in it selected and
EVERYTHING ELSE deleted.  Make Sense?  

EX:
If Column A has Whse: ## AND Column B has Source: ###   Then Delete
everything else.  

I know it's a mess and I'm getting nowhere.  Any input would be GREATLY
appreciated.  THANKS!!!
Tom Ogilvy - 24 May 2006 20:26 GMT
to not delete both columns A and B on that row must contain the strings

set lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
 if instr(1,cells(i,1),"Whse: ", vbTextCompare) > 0 and _
   instr(1,cells(k,2),"Source: ", vbTextCompare) > 0 then
      ' do nothing
 else
       if rng is nothing then
          set rng = cells(i,1)
        else
          set rng = union(rng,cells(i,1))
        end if  
   end if
Next
if not rng is nothing then
 rng.Entirerow.Delete
End if

or
If either contains the specific string, then don't delete

set lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
 if instr(1,cells(i,1),"Whse: ", vbTextCompare) > 0 or _
   instr(1,cells(k,2),"Source: ", vbTextCompare) > 0 then
      ' do nothing
 else
       if rng is nothing then
          set rng = cells(i,1)
        else
          set rng = union(rng,cells(i,1))
        end if  
   end if
Next
if not rng is nothing then
 rng.Entirerow.Delete
End if

Signature

Regards,
Tom Ogilvy

> I am trying to sort for some pretty specific stuff and delete everything
> else.  
[quoted text clipped - 10 lines]
> I know it's a mess and I'm getting nowhere.  Any input would be GREATLY
> appreciated.  THANKS!!!
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.