Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Deleting Rows Problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default Deleting Rows Problem
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Deleting Rows Problem
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" wrote in message
.. .
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default Deleting Rows Problem
Hi There,

this code gives me a "Run-Time error type mismatvh 13"

reegards Harry

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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" wrote in message
.. .
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






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 37
Default Deleting Rows Problem
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
...
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" wrote in message
...
Hi There,

this code gives me a "Run-Time error type mismatvh 13"

reegards Harry

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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" wrote in message
.. .
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










Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default Deleting Rows Problem
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" wrote in message
...
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
...
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" wrote in message
...
Hi There,

this code gives me a "Run-Time error type mismatvh 13"

reegards Harry

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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" wrote in message
.. .
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












Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem deleting rows by index Chris Excel Programming 6 June 13th 06 01:11 AM
Problem Deleting all rows from List Object Dean[_10_] Excel Programming 1 April 19th 06 02:36 PM
Help!! I have problem deleting 2500 rows of filtered rows!!!! shirley_kee Excel Discussion (Misc queries) 1 January 12th 06 03:24 AM
Help!!! I have problem deleting 2500 rows of filtered rows shirley_kee[_2_] Excel Programming 1 January 12th 06 03:15 AM
Problem occur in sum function while deleting the rows Ellis Yu Excel Worksheet Functions 0 October 26th 05 02:50 AM


All times are GMT +1. The time now is 11:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement