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 / March 2006

Tip: Looking for answers? Try searching our database.

deleting unneeded rows

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Brian Matlack - 20 Mar 2006 18:19 GMT
Hi!
I want to delete all rows that do not contain the numbers 1, 2, 3 or
in column A. (some column A cells have letters and some are blank thes
are the ones I want to delete) How can I do this with a macro?
Thanks!
Ron de Bruin - 20 Mar 2006 18:59 GMT
You can try this Brian

Sub Example1()
   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

   ViewMode = ActiveWindow.View
   ActiveWindow.View = xlNormalView

   Firstrow = ActiveSheet.UsedRange.Cells(1).Row
   Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

   With ActiveSheet
       .DisplayPageBreaks = False
       For Lrow = Lastrow To Firstrow Step -1

           If IsError(.Cells(Lrow, "A").Value) Then
               'Do nothing, This avoid a error if there is a error in the cell

           ElseIf .Cells(Lrow, "A").Value <> "1" And _
                  .Cells(Lrow, "A").Value <> "2" And _
                  .Cells(Lrow, "A").Value <> "3" And _
                  .Cells(Lrow, "A").Value <> "4" Then .Rows(Lrow).Delete

           End If
       Next
   End With

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

End Sub

Signature

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

> Hi!
> I want to delete all rows that do not contain the numbers 1, 2, 3 or 4
> in column A. (some column A cells have letters and some are blank these
> are the ones I want to delete) How can I do this with a macro?
> Thanks!!
Brian Matlack - 20 Mar 2006 20:26 GMT
Ron de Bruin Wrote:
> You can try this Brian
>
[quoted text clipped - 66 lines]
> > View this thread
> http://www.excelforum.com/showthread.php?threadid=524463

Thanks Ron this works GREAT!
Tom Ogilvy - 20 Mar 2006 19:03 GMT
Dim rng as Range, i as Long, lastrow as Long
On error resume next
 set rng = Columns(1).Specialcells(xlblanks)
 rng.EntireRow.Delete
 set rng = columns(1).Specialcells(xlConstants,xlTextValues)
 rng.Entirerow.Delete
On error go 0
lastrow = cells(rows.count,1).End(xlup).row
for i = lastrow to 1 step -1
 set cell = cells(i,1)
 if isnumeric(cell) then
   if cell>4 or cell < 1 then
     rows(i).Delete
   end if
 else
    rows(i).delete
 end if
Next

Untested pseudocode.
Signature

Regards,
Tom Ogilvy

 

> Hi!
> I want to delete all rows that do not contain the numbers 1, 2, 3 or 4
> in column A. (some column A cells have letters and some are blank these
> are the ones I want to delete) How can I do this with a macro?
> Thanks!!
Brian Matlack - 20 Mar 2006 20:27 GMT
Tom Ogilvy Wrote:
> Dim rng as Range, i as Long, lastrow as Long
> On error resume next
[quoted text clipped - 36 lines]
> > View this thread
> http://www.excelforum.com/showthread.php?threadid=524463

Thanks Tom this one works Great also!
 
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.