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 / New Users / August 2007

Tip: Looking for answers? Try searching our database.

Filtering or deleting multiple rows

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Determined07 - 15 Aug 2007 21:18 GMT
I'm not exactly a new user, but this is the first time in a while that I've
had to apply this knowledge to my professional duties. The spreadsheet I'm
working on is considerably large and I need to retain rows that contain
certain text or specific criteria. For example, I only need items A, C, H,
and P, and Z out of a document that has everything else (i don't need) in
between. How do I delete based on specific criteria (letter, city name,)
withouth scrolling down and sifting through it manually? Please help me.
Ron de Bruin - 15 Aug 2007 21:43 GMT
Start here
http://www.rondebruin.nl/delete.htm

For example try this one

This will check column A (Change it to the column you want)
'We check the values in the A column in this example
           With .Cells(Lrow, "A")

Add the other values to the array in the macro

                   If IsError(Application.Match(.Value, _
                   Array("A", "C", "H"), 0)) Then .EntireRow.Delete

You can also add a list of values on another worksheet if you find that easy ?

Sub Loop_Example()
   Dim Firstrow As Long
   Dim Lastrow As Long
   Dim Lrow As Long
   Dim CalcMode As Long
   Dim ViewMode As Long

   With Application
       CalcMode = .Calculation
       .Calculation = xlCalculationManual
       .ScreenUpdating = False
   End With

   'We use the ActiveSheet but you can replace this with
   'Sheets("MySheet")if you want
   With ActiveSheet

       'We select the sheet so we can change the window view
       .Select

       'If you are in Page Break Preview Or Page Layout view go
       'back to normal view, we do this for speed
       ViewMode = ActiveWindow.View
       ActiveWindow.View = xlNormalView

       'Turn off Page Breaks, we do this for speed
       .DisplayPageBreaks = False

       'Set the first and last row to loop through
       Firstrow = .UsedRange.Cells(1).Row
       Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

       'We loop from Lastrow to Firstrow (bottom to top)
       For Lrow = Lastrow To Firstrow Step -1

           'We check the values in the A column in this example
           With .Cells(Lrow, "A")

               If Not IsError(.Value) Then

                   If IsError(Application.Match(.Value, _
                   Array("A", "C", "H"), 0)) Then .EntireRow.Delete

               End If

           End With

       Next Lrow

   End With

   ActiveWindow.View = ViewMode
   With Application
       .ScreenUpdating = True
       .Calculation = CalcMode
   End With

End Sub

Signature

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

> I'm not exactly a new user, but this is the first time in a while that I've
> had to apply this knowledge to my professional duties. The spreadsheet I'm
[quoted text clipped - 3 lines]
> between. How do I delete based on specific criteria (letter, city name,)
> withouth scrolling down and sifting through it manually? Please help me.
Determined07 - 15 Aug 2007 22:04 GMT
Thanks for that. I need to delete the rows if a specific value does NOT
exist. The amount of items that I need to delete is greater than those I need
to retain, so it will be simpler to eliminate that bulk all at once. How do I
do that? Please help me...there's this project going on and I've been so
frustrated.

Also, once it's deleted, I need to add a new column with text to
corresponding with the remaining text (that I retained) from the other column.

for example: F7 to F765 and j7 to j765 should be consistent with eachother.
I really feel at a loss.

> Start here
> http://www.rondebruin.nl/delete.htm
[quoted text clipped - 78 lines]
> > between. How do I delete based on specific criteria (letter, city name,)
> > withouth scrolling down and sifting through it manually? Please help me.
Ron de Bruin - 15 Aug 2007 22:22 GMT
Hi Determined07

>> Thanks for that. I need to delete the rows if a specific value does NOT
The macro do that

If IsError(Application.Match

If Not IsError(Application.Match ...........delete the items in the array

>> to retain, so it will be simpler to eliminate that bulk all at once

AutoFilter have a maximum of 2 criteria

Advanced filer is another option but you must add the values in a range
Start here
http://www.contextures.com/xladvfilter01.html

With my example above you can also fill in the values in a range
'Replace Array("A", "C", "H") with  Sheets("Sheet1").Range("A1:A200")

There is also a Union example on my site that is faster if it is a large range

Signature

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

> Thanks for that. I need to delete the rows if a specific value does NOT
> exist. The amount of items that I need to delete is greater than those I need
[quoted text clipped - 90 lines]
>> > between. How do I delete based on specific criteria (letter, city name,)
>> > withouth scrolling down and sifting through it manually? Please help me.
Determined07 - 17 Aug 2007 02:19 GMT
instead of editing the initial document and deleting from that particular
worksheet, would it be better (think in terms of a new user) to create a
whole new worksheet altogether, and pull the data that meets the criteria? If
that's recommended, please tell me how to do that. Thanks, you've  been a
great help.

> Hi Determined07
>
[quoted text clipped - 112 lines]
> >> > between. How do I delete based on specific criteria (letter, city name,)
> >> > withouth scrolling down and sifting through it manually? Please help me.
Ron de Bruin - 17 Aug 2007 21:30 GMT
Hi

You can use SaveCopyAs to save the workbook with a new name and open that with
Workbooks.Open................................
See VBA help for more details

If you need more help post back

Signature

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

> instead of editing the initial document and deleting from that particular
> worksheet, would it be better (think in terms of a new user) to create a
[quoted text clipped - 118 lines]
>> >> > between. How do I delete based on specific criteria (letter, city name,)
>> >> > withouth scrolling down and sifting through it manually? Please help me.
 
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.