 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2006Delete Rows then Move Up
Hi... I have 3000 records. In column B there are sporadic #N/A values. I need some nicely packaged code that will delete all rows where a #N/A appears in B, so that there are no gaps in the rows. Sounds easy but I'm struggling. Any help would be most appreciated. Thanks Gordon... In the Worksheet module: Sub Delete_NA() Dim MyCell As Range For Each MyCell In Range("B1:B3000") If MyCell.Value = "N/A#" Then MyCell.EntireRow.Delete End If Next End Sub > Hi... > [quoted text clipped - 7 lines] > > Gordon... Hi... The procedure stops on this line: Run-time error 13 Type mismatch If MyCell.Value = "#N/A" Then Any clues? Thanks in advance... Gordon... > In the Worksheet module: > [quoted text clipped - 18 lines] > > > > Gordon... Try this. It looks for the N/A error. Sub Delete_NA() Dim MyCell As Range For Each MyCell In Range("B1:B3000") If Application.WorksheetFunction.IsNA(MyCell) = True Then MyCell.EntireRow.Delete End If Next End Sub > Hi... > [quoted text clipped - 30 lines] > > > > > > Gordon... Gordon, Try this. It checks for the N/A error. Alan Sub Delete_NA() Dim MyCell As Range For Each MyCell In Range("B1:B3000") If Application.WorksheetFunction.IsNA(MyCell) = True Then MyCell.EntireRow.Delete End If Next End Sub > Hi... > [quoted text clipped - 30 lines] > > > > > > Gordon... Hi... Thought about it for 30 seconds, the screen flickered, but then it did nothing... Any clues? Cheers Gordon... > Gordon, > [quoted text clipped - 45 lines] > > > > > > > > Gordon... I ran it on some test data and it worked fine. I will take a look at it again. Do you have any other code that may be firing as a result of changes in the worksheet? > Hi... > [quoted text clipped - 56 lines] > > > > > > > > > > Gordon... Sub DeleteNA() With Activesheet On Error Resume Next .columns(2).SpecialCells(xlFormulas,xlErrors).EntireRow.Delete On Error goto 0 End With End Sub  SignatureRegards, Tom Ogilvy >I ran it on some test data and it worked fine. I will take a look at > it again. Do you have any other code that may be firing as a result of [quoted text clipped - 63 lines] >> > > > > >> > > > > Gordon... Hi Tom... The #N/A that I need deleting was being created by an error response to a vlookup table. By using iserror in my formula the #n/a is now a 1. I think deleting #n/a's was too problematic. Is it easier to delete rows where a 1 occours in column b, rather than #n/a's? Thanks Gordon > Sub DeleteNA() > With Activesheet [quoted text clipped - 71 lines] > >> > > > > > >> > > > > Gordon... No, it is much easier to delete the #N/A's as I have shown. And then you don't have to use iserror in your formula. If, in your current situation, the formula always returns a text value when successful and the number 1 when not, then you could still do Sub DeleteNA() With Activesheet On Error Resume Next .columns(2).SpecialCells(xlFormulas,xlNumbers).EntireRow.Delete On Error goto 0 End With End Sub so any row with a formula returning a number would get deleted -- if your formula were like: =if(iserror(vlookup),1,vlookup) SignatureRegards, Tom Ogilvy > Hi Tom... > [quoted text clipped - 83 lines] >> >> > > > > >> >> > > > > Gordon... |
Reply to this Message |
 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.