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 / January 2007

Tip: Looking for answers? Try searching our database.

delete entire row based on conditional test of column A

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
guillermo.ht - 22 Jan 2007 03:27 GMT
I have a spreadsheet with various invoice numbers in column A:
For example:
FC110107-002
PB110107-001
LV010707-001

I need a VBA macro that will delete delete an entire row if the LEFT 2
characters of a cell in column A have either: FC, PB, or GR

The default range of my data is usually rows 2 - 2000

I cannot seem to find other snippets of code that work for my needs, so any
help would be appreciated.
Chip Pearson - 22 Jan 2007 03:36 GMT
Try something like the following:

Sub AAA()

   Dim LastRow As Long
   Dim FirstRow As Long
   Dim RowNdx As Long
   Dim WS As Worksheet

   FirstRow = 1 '<<< CHANGE AS REQUIRED
   Set WS = Worksheets("Sheet1") '<<< CHANGE AS REQUIRED

   With WS
       LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       For RowNdx = LastRow To FirstRow Step -1
           Select Case Left(.Cells(RowNdx, "A").Value, 2)
               Case "FC", "PB", "GR"
                   .Rows(RowNdx).Delete shift:=xlUp
               Case Else
                   ' do nothing
           End Select
       Next RowNdx
   End With

End Sub

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

>I have a spreadsheet with various invoice numbers in column A:
> For example:
[quoted text clipped - 10 lines]
> any
> help would be appreciated.
guillermo.ht - 22 Jan 2007 03:49 GMT
Thank you very much. This worked perfectly and has saved me a serious headache.

> Try something like the following:
>
[quoted text clipped - 36 lines]
> > any
> > help would be appreciated.
 
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.