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 / Worksheet Functions / August 2005

Tip: Looking for answers? Try searching our database.

Deleting rows based on a cell value

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JPriest - 11 Aug 2005 14:22 GMT
Good morning,
I have a very large worksheet, some 15,000 entries, and I would like to
delete all the rows with "no" in col G. Is there a macro for this?

Thank you in advance,
Jeff

Signature

JPriest

Ron de Bruin - 11 Aug 2005 16:56 GMT
Hi JPriest

Look here for examples
http://www.rondebruin.nl/delete.htm

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> Good morning,
> I have a very large worksheet, some 15,000 entries, and I would like to
> delete all the rows with "no" in col G. Is there a macro for this?
>
> Thank you in advance,
> Jeff
JPriest - 11 Aug 2005 22:20 GMT
Hi Ron,

I checked that link out this morning and followed the instructions, bu
everytime I ran the Macro, i got an error, 4683 if i recall correctly.

Any suggestions?

As a follow-up; what's a good source to learn VBA? any suggeste
sites/readings?

To do more, I'd like to know more
Ron de Bruin - 12 Aug 2005 17:44 GMT
Hi JPriest

Which example do you use?

Try this first :

Here is a filter example
In this Example "G1" is the header cell.

If your header start in G4 then change the range to G4:G15000

Sub Delete_with_Autofilter()
   Dim DeleteValue As String
   Dim rng As Range

   DeleteValue = "no"
   ' This will delete the rows with "no" in the Range("G1:G15000")
   With ActiveSheet
       .Range("G1:G15000").AutoFilter Field:=1, Criteria1:=DeleteValue
       With ActiveSheet.AutoFilter.Range
           On Error Resume Next
           Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                     .SpecialCells(xlCellTypeVisible)
           On Error GoTo 0
           If Not rng Is Nothing Then rng.EntireRow.Delete

       End With
       .AutoFilterMode = False
   End With
End Sub

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> Hi Ron,
>
[quoted text clipped - 7 lines]
>
> To do more, I'd like to know more!
 
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.