| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Big H
Guest
Posts: n/a
|
Hi There,
The code below works almost perfectly, however it does not delete rows which have #N/A. I believe it has got something to do with the iserror part of the code. Any help would be appreciated. tia Harry If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "#N/A" in Column I, case sensitive. Sub DeleteRows() 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, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete 'This will delete each row with the Value "CSVS" in Column I, case sensitive. End If Next With ActiveSheet .DisplayPageBreaks = False For Lrow = LastRow To Firstrow Step -1 If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete 'This will delete each row with the Value "CMPN" in Column I, case sensitive. End If Next With ActiveSheet .DisplayPageBreaks = False For Lrow = LastRow To Firstrow Step -1 If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete 'This will delete each row with the Value "CMPN" in Column I, case sensitive. End If Next With ActiveSheet .DisplayPageBreaks = False For Lrow = LastRow To Firstrow Step -1 If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "#N/A" in Column I, case sensitive. End If Next With ActiveSheet .DisplayPageBreaks = False For Lrow = LastRow To Firstrow Step -1 If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete 'This will delete each row with the Value "EXTN" in Column I, case sensitive. End If Next With ActiveSheet .DisplayPageBreaks = False For Lrow = LastRow To Firstrow Step -1 If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete 'This will delete each row with the Value "EXRP" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End With End With End With End With End With End Sub |
|
||
|
||||
|
|
|
| |
|
Gary Keramidas
Guest
Posts: n/a
|
this is untested. i left justified the comments to hopefully keep them from
wrapping Sub DeleteRows() 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 .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "#N/A" in Column I, case sensitive. If IsError(.Cells(Lrow, "I").Value) Then 'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete 'This will delete each row with the Value "CSVS" in Column I, case sensitive. ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete 'This will delete each row with the Value "CMPN" in Column I, case sensitive. ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete 'This will delete each row with the Value "CMPN" in Column I, case sensitive. ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete 'This will delete each row with the Value "#N/A" in Column I, case sensitive. ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete 'This will delete each row with the Value "EXTN" in Column I, case sensitive. ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete 'This will delete each row with the Value "EXRP" in Column A, case sensitive. End If Next End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub -- Gary "Big H" <(E-Mail Removed)> wrote in message news:%23l%23dmSd%(E-Mail Removed)... > Hi There, > > The code below works almost perfectly, however it does not delete rows which > have #N/A. I believe it has got something to do with the iserror part of the > code. Any help would be appreciated. > > tia Harry > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "#N/A" in Column I, > case sensitive. > > > > > Sub DeleteRows() > 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, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "CSVS" in Column I, > case sensitive. > > End If > Next > With ActiveSheet > .DisplayPageBreaks = False > For Lrow = LastRow To Firstrow Step -1 > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "CMPN" in Column I, > case sensitive. > > End If > Next > With ActiveSheet > .DisplayPageBreaks = False > For Lrow = LastRow To Firstrow Step -1 > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "CMPN" in Column I, > case sensitive. > > End If > Next > With ActiveSheet > .DisplayPageBreaks = False > For Lrow = LastRow To Firstrow Step -1 > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "#N/A" in Column I, > case sensitive. > > End If > Next > With ActiveSheet > .DisplayPageBreaks = False > For Lrow = LastRow To Firstrow Step -1 > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "EXTN" in Column I, > case sensitive. > > End If > Next > With ActiveSheet > .DisplayPageBreaks = False > For Lrow = LastRow To Firstrow Step -1 > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "EXRP" in Column A, > case sensitive. > > End If > Next > End With > > ActiveWindow.View = ViewMode > With Application > .ScreenUpdating = True > .Calculation = CalcMode > End With > End With > End With > End With > End With > End With > End Sub > > |
|
||
|
|
||||
|
Big H
Guest
Posts: n/a
|
Hi There,
this code gives me a "Run-Time error type mismatvh 13" reegards Harry "Gary Keramidas" <GKeramidasATmsn.com> wrote in message news:ucwpAbd%(E-Mail Removed)... > this is untested. i left justified the comments to hopefully keep them > from wrapping > > Sub DeleteRows() > 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 .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "#N/A" in Column I, case > sensitive. > > > If IsError(.Cells(Lrow, "I").Value) Then > 'Do nothing, This avoid a error if there is a error in the cell > > ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "CSVS" in Column I, case > sensitive. > ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "CMPN" in Column I, case > sensitive. > ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "CMPN" in Column I, case > sensitive. > ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "#N/A" in Column I, case > sensitive. > ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "EXTN" in Column I, case > sensitive. > ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete > 'This will delete each row with the Value "EXRP" in Column A, case > sensitive. > End If > Next > > End With > ActiveWindow.View = ViewMode > With Application > .ScreenUpdating = True > .Calculation = CalcMode > End With > > End Sub > > > > > -- > > > Gary > > > "Big H" <(E-Mail Removed)> wrote in message > news:%23l%23dmSd%(E-Mail Removed)... >> Hi There, >> >> The code below works almost perfectly, however it does not delete rows >> which have #N/A. I believe it has got something to do with the iserror >> part of the code. Any help would be appreciated. >> >> tia Harry >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "#N/A" in Column >> I, case sensitive. >> >> >> >> >> Sub DeleteRows() >> 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, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then >> .Rows(Lrow).Delete >> 'This will delete each row with the Value "CSVS" in Column >> I, case sensitive. >> >> End If >> Next >> With ActiveSheet >> .DisplayPageBreaks = False >> For Lrow = LastRow To Firstrow Step -1 >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then >> .Rows(Lrow).Delete >> 'This will delete each row with the Value "CMPN" in Column >> I, case sensitive. >> >> End If >> Next >> With ActiveSheet >> .DisplayPageBreaks = False >> For Lrow = LastRow To Firstrow Step -1 >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then >> .Rows(Lrow).Delete >> 'This will delete each row with the Value "CMPN" in Column >> I, case sensitive. >> >> End If >> Next >> With ActiveSheet >> .DisplayPageBreaks = False >> For Lrow = LastRow To Firstrow Step -1 >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "#N/A" in Column >> I, case sensitive. >> >> End If >> Next >> With ActiveSheet >> .DisplayPageBreaks = False >> For Lrow = LastRow To Firstrow Step -1 >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then >> .Rows(Lrow).Delete >> 'This will delete each row with the Value "EXTN" in Column >> I, case sensitive. >> >> End If >> Next >> With ActiveSheet >> .DisplayPageBreaks = False >> For Lrow = LastRow To Firstrow Step -1 >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the >> cell >> >> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then >> .Rows(Lrow).Delete >> 'This will delete each row with the Value "EXRP" in Column >> A, case sensitive. >> >> End If >> Next >> End With >> >> ActiveWindow.View = ViewMode >> With Application >> .ScreenUpdating = True >> .Calculation = CalcMode >> End With >> End With >> End With >> End With >> End With >> End With >> End Sub >> >> > > |
|
||
|
|
||||
|
Gary Keramidas
Guest
Posts: n/a
|
i don't have your data, but i just pasted the code in a worksheet and it runs
without any errors. do you get an error on compile? if so, make sure it pasted in your module ok without word wrapping -- Gary "Big H" <(E-Mail Removed)> wrote in message news:uu1V93d%(E-Mail Removed)... > Hi There, > > this code gives me a "Run-Time error type mismatvh 13" > > reegards Harry > > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message > news:ucwpAbd%(E-Mail Removed)... >> this is untested. i left justified the comments to hopefully keep them from >> wrapping >> >> Sub DeleteRows() >> 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 .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "#N/A" in Column I, case sensitive. >> >> >> If IsError(.Cells(Lrow, "I").Value) Then >> 'Do nothing, This avoid a error if there is a error in the cell >> >> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "CSVS" in Column I, case sensitive. >> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "CMPN" in Column I, case sensitive. >> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "CMPN" in Column I, case sensitive. >> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "#N/A" in Column I, case sensitive. >> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "EXTN" in Column I, case sensitive. >> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete >> 'This will delete each row with the Value "EXRP" in Column A, case sensitive. >> End If >> Next >> >> End With >> ActiveWindow.View = ViewMode >> With Application >> .ScreenUpdating = True >> .Calculation = CalcMode >> End With >> >> End Sub >> >> >> >> >> -- >> >> >> Gary >> >> >> "Big H" <(E-Mail Removed)> wrote in message >> news:%23l%23dmSd%(E-Mail Removed)... >>> Hi There, >>> >>> The code below works almost perfectly, however it does not delete rows which >>> have #N/A. I believe it has got something to do with the iserror part of the >>> code. Any help would be appreciated. >>> >>> tia Harry >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "#N/A" in Column I, >>> case sensitive. >>> >>> >>> >>> >>> Sub DeleteRows() >>> 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, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "CSVS" in Column I, >>> case sensitive. >>> >>> End If >>> Next >>> With ActiveSheet >>> .DisplayPageBreaks = False >>> For Lrow = LastRow To Firstrow Step -1 >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "CMPN" in Column I, >>> case sensitive. >>> >>> End If >>> Next >>> With ActiveSheet >>> .DisplayPageBreaks = False >>> For Lrow = LastRow To Firstrow Step -1 >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "CMPN" in Column I, >>> case sensitive. >>> >>> End If >>> Next >>> With ActiveSheet >>> .DisplayPageBreaks = False >>> For Lrow = LastRow To Firstrow Step -1 >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "#N/A" in Column I, >>> case sensitive. >>> >>> End If >>> Next >>> With ActiveSheet >>> .DisplayPageBreaks = False >>> For Lrow = LastRow To Firstrow Step -1 >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "EXTN" in Column I, >>> case sensitive. >>> >>> End If >>> Next >>> With ActiveSheet >>> .DisplayPageBreaks = False >>> For Lrow = LastRow To Firstrow Step -1 >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the >>> cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "EXRP" in Column A, >>> case sensitive. >>> >>> End If >>> Next >>> End With >>> >>> ActiveWindow.View = ViewMode >>> With Application >>> .ScreenUpdating = True >>> .Calculation = CalcMode >>> End With >>> End With >>> End With >>> End With >>> End With >>> End With >>> End Sub >>> >>> >> >> > > |
|
||
|
|
||||
|
Big H
Guest
Posts: n/a
|
Gary,
the error comes when one of the rows has #N/A, what I want the code to do is delete the row if it shows #N/A or any other criteria I have shown inthe code. "Gary Keramidas" <GKeramidasATmsn.com> wrote in message news:%23f4Yxkf%(E-Mail Removed)... >i don't have your data, but i just pasted the code in a worksheet and it >runs without any errors. > > do you get an error on compile? > if so, make sure it pasted in your module ok without word wrapping > -- > > > Gary > > > "Big H" <(E-Mail Removed)> wrote in message > news:uu1V93d%(E-Mail Removed)... >> Hi There, >> >> this code gives me a "Run-Time error type mismatvh 13" >> >> reegards Harry >> >> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message >> news:ucwpAbd%(E-Mail Removed)... >>> this is untested. i left justified the comments to hopefully keep them >>> from wrapping >>> >>> Sub DeleteRows() >>> 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 .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>> 'This will delete each row with the Value "#N/A" in Column I, case >>> sensitive. >>> >>> >>> If IsError(.Cells(Lrow, "I").Value) Then >>> 'Do nothing, This avoid a error if there is a error in the cell >>> >>> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then >>> .Rows(Lrow).Delete >>> 'This will delete each row with the Value "CSVS" in Column I, case >>> sensitive. >>> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then >>> .Rows(Lrow).Delete >>> 'This will delete each row with the Value "CMPN" in Column I, case >>> sensitive. >>> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then >>> .Rows(Lrow).Delete >>> 'This will delete each row with the Value "CMPN" in Column I, case >>> sensitive. >>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then >>> .Rows(Lrow).Delete >>> 'This will delete each row with the Value "#N/A" in Column I, case >>> sensitive. >>> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then >>> .Rows(Lrow).Delete >>> 'This will delete each row with the Value "EXTN" in Column I, case >>> sensitive. >>> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then >>> .Rows(Lrow).Delete >>> 'This will delete each row with the Value "EXRP" in Column A, case >>> sensitive. >>> End If >>> Next >>> >>> End With >>> ActiveWindow.View = ViewMode >>> With Application >>> .ScreenUpdating = True >>> .Calculation = CalcMode >>> End With >>> >>> End Sub >>> >>> >>> >>> >>> -- >>> >>> >>> Gary >>> >>> >>> "Big H" <(E-Mail Removed)> wrote in message >>> news:%23l%23dmSd%(E-Mail Removed)... >>>> Hi There, >>>> >>>> The code below works almost perfectly, however it does not delete rows >>>> which have #N/A. I believe it has got something to do with the iserror >>>> part of the code. Any help would be appreciated. >>>> >>>> tia Harry >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "#N/A" in >>>> Column I, case sensitive. >>>> >>>> >>>> >>>> >>>> Sub DeleteRows() >>>> 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, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "CSVS" in >>>> Column I, case sensitive. >>>> >>>> End If >>>> Next >>>> With ActiveSheet >>>> .DisplayPageBreaks = False >>>> For Lrow = LastRow To Firstrow Step -1 >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "CMPN" in >>>> Column I, case sensitive. >>>> >>>> End If >>>> Next >>>> With ActiveSheet >>>> .DisplayPageBreaks = False >>>> For Lrow = LastRow To Firstrow Step -1 >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "CMPN" in >>>> Column I, case sensitive. >>>> >>>> End If >>>> Next >>>> With ActiveSheet >>>> .DisplayPageBreaks = False >>>> For Lrow = LastRow To Firstrow Step -1 >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "#N/A" in >>>> Column I, case sensitive. >>>> >>>> End If >>>> Next >>>> With ActiveSheet >>>> .DisplayPageBreaks = False >>>> For Lrow = LastRow To Firstrow Step -1 >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "EXTN" in >>>> Column I, case sensitive. >>>> >>>> End If >>>> Next >>>> With ActiveSheet >>>> .DisplayPageBreaks = False >>>> For Lrow = LastRow To Firstrow Step -1 >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in >>>> the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then >>>> .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "EXRP" in >>>> Column A, case sensitive. >>>> >>>> End If >>>> Next >>>> End With >>>> >>>> ActiveWindow.View = ViewMode >>>> With Application >>>> .ScreenUpdating = True >>>> .Calculation = CalcMode >>>> End With >>>> End With >>>> End With >>>> End With >>>> End With >>>> End With >>>> End Sub >>>> >>>> >>> >>> >> >> > > |
|
||
|
|
||||
|
Gary Keramidas
Guest
Posts: n/a
|
maybe try reversing these 2 lines
If .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete If IsError(.Cells(Lrow, "I").Value) Then so it's like this: If IsError(.Cells(Lrow, "I").Value) Then If .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete other than that, maybe someone else will help out -- Gary "Big H" <(E-Mail Removed)> wrote in message news:O5nzuqr%(E-Mail Removed)... > Gary, > > the error comes when one of the rows has #N/A, what I want the code to do is > delete the row if it shows #N/A or any other criteria I have shown inthe code. > > > "Gary Keramidas" <GKeramidasATmsn.com> wrote in message > news:%23f4Yxkf%(E-Mail Removed)... >>i don't have your data, but i just pasted the code in a worksheet and it runs >>without any errors. >> >> do you get an error on compile? >> if so, make sure it pasted in your module ok without word wrapping >> -- >> >> >> Gary >> >> >> "Big H" <(E-Mail Removed)> wrote in message >> news:uu1V93d%(E-Mail Removed)... >>> Hi There, >>> >>> this code gives me a "Run-Time error type mismatvh 13" >>> >>> reegards Harry >>> >>> "Gary Keramidas" <GKeramidasATmsn.com> wrote in message >>> news:ucwpAbd%(E-Mail Removed)... >>>> this is untested. i left justified the comments to hopefully keep them from >>>> wrapping >>>> >>>> Sub DeleteRows() >>>> 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 .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "#N/A" in Column I, case >>>> sensitive. >>>> >>>> >>>> If IsError(.Cells(Lrow, "I").Value) Then >>>> 'Do nothing, This avoid a error if there is a error in the cell >>>> >>>> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "CSVS" in Column I, case >>>> sensitive. >>>> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "CMPN" in Column I, case >>>> sensitive. >>>> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "CMPN" in Column I, case >>>> sensitive. >>>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "#N/A" in Column I, case >>>> sensitive. >>>> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "EXTN" in Column I, case >>>> sensitive. >>>> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete >>>> 'This will delete each row with the Value "EXRP" in Column A, case >>>> sensitive. >>>> End If >>>> Next >>>> >>>> End With >>>> ActiveWindow.View = ViewMode >>>> With Application >>>> .ScreenUpdating = True >>>> .Calculation = CalcMode >>>> End With >>>> >>>> End Sub >>>> >>>> >>>> >>>> >>>> -- >>>> >>>> >>>> Gary >>>> >>>> >>>> "Big H" <(E-Mail Removed)> wrote in message >>>> news:%23l%23dmSd%(E-Mail Removed)... >>>>> Hi There, >>>>> >>>>> The code below works almost perfectly, however it does not delete rows >>>>> which have #N/A. I believe it has got something to do with the iserror >>>>> part of the code. Any help would be appreciated. >>>>> >>>>> tia Harry >>>>> >>>>> If IsError(.Cells(Lrow, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "#N/A" in Column >>>>> I, case sensitive. >>>>> >>>>> >>>>> >>>>> >>>>> Sub DeleteRows() >>>>> 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, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "CSVS" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "CSVS" in Column >>>>> I, case sensitive. >>>>> >>>>> End If >>>>> Next >>>>> With ActiveSheet >>>>> .DisplayPageBreaks = False >>>>> For Lrow = LastRow To Firstrow Step -1 >>>>> >>>>> If IsError(.Cells(Lrow, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "CMPN" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "CMPN" in Column >>>>> I, case sensitive. >>>>> >>>>> End If >>>>> Next >>>>> With ActiveSheet >>>>> .DisplayPageBreaks = False >>>>> For Lrow = LastRow To Firstrow Step -1 >>>>> >>>>> If IsError(.Cells(Lrow, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "RMAT" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "CMPN" in Column >>>>> I, case sensitive. >>>>> >>>>> End If >>>>> Next >>>>> With ActiveSheet >>>>> .DisplayPageBreaks = False >>>>> For Lrow = LastRow To Firstrow Step -1 >>>>> >>>>> If IsError(.Cells(Lrow, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "#N/A" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "#N/A" in Column >>>>> I, case sensitive. >>>>> >>>>> End If >>>>> Next >>>>> With ActiveSheet >>>>> .DisplayPageBreaks = False >>>>> For Lrow = LastRow To Firstrow Step -1 >>>>> >>>>> If IsError(.Cells(Lrow, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "EXTN" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "EXTN" in Column >>>>> I, case sensitive. >>>>> >>>>> End If >>>>> Next >>>>> With ActiveSheet >>>>> .DisplayPageBreaks = False >>>>> For Lrow = LastRow To Firstrow Step -1 >>>>> >>>>> If IsError(.Cells(Lrow, "I").Value) Then >>>>> 'Do nothing, This avoid a error if there is a error in the >>>>> cell >>>>> >>>>> ElseIf .Cells(Lrow, "I").Value = "EXRP" Then .Rows(Lrow).Delete >>>>> 'This will delete each row with the Value "EXRP" in Column >>>>> A, case sensitive. >>>>> >>>>> End If >>>>> Next >>>>> End With >>>>> >>>>> ActiveWindow.View = ViewMode >>>>> With Application >>>>> .ScreenUpdating = True >>>>> .Calculation = CalcMode >>>>> End With >>>>> End With >>>>> End With >>>>> End With >>>>> End With >>>>> End With >>>>> End Sub >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > |
|
||
|
|
||||
|
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Problem deleting rows by index | Chris | Microsoft Excel Programming | 6 | 13th Jun 2006 01:11 AM |
| Help!! I have problem deleting 2500 rows of filtered rows!!!! | shirley_kee | Microsoft Excel Misc | 1 | 12th Jan 2006 03:24 AM |
| Help!!! I have problem deleting 2500 rows of filtered rows | shirley_kee | Microsoft Excel Programming | 1 | 12th Jan 2006 03:15 AM |
| Re: Having A Problem Deleting Rows | Ron de Bruin | Microsoft Excel Misc | 0 | 21st Sep 2003 09:06 AM |
| Having A Problem Deleting Rows | Elise | Microsoft Excel Worksheet Functions | 1 | 21st Sep 2003 09:05 AM |
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc. |
