Re: Deleting Rows Problem
- From: "Gary Keramidas" <GKeramidasATmsn.com>
- Date: Sat, 28 Oct 2006 14:43:20 -0400
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" <hsjewebster@xxxxxxx> wrote in message
news:O5nzuqr%23GHA.924@xxxxxxxxxxxxxxxxxxxxxxx
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%23GHA.3344@xxxxxxxxxxxxxxxxxxxxxxx
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" <hsjewebster@xxxxxxx> wrote in message
news:uu1V93d%23GHA.748@xxxxxxxxxxxxxxxxxxxxxxx
Hi There,
this code gives me a "Run-Time error type mismatvh 13"
reegards Harry
"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:ucwpAbd%23GHA.3352@xxxxxxxxxxxxxxxxxxxxxxx
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" <hsjewebster@xxxxxxx> wrote in message
news:%23l%23dmSd%23GHA.3644@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- Deleting Rows Problem
- From: Big H
- Re: Deleting Rows Problem
- From: Gary Keramidas
- Re: Deleting Rows Problem
- From: Big H
- Re: Deleting Rows Problem
- From: Gary Keramidas
- Re: Deleting Rows Problem
- From: Big H
- Deleting Rows Problem
- Prev by Date: Re: Macro to replace file name many times
- Next by Date: Re: Program to insert rows
- Previous by thread: Re: Deleting Rows Problem
- Next by thread: HELP!!
- Index(es):
Relevant Pages
|
