deleting row using macro

G

Gautam

Hi,

I am trying to delete rows in specific columns that have certain words
in them. I am a total newbie.

The error is: "Runtime Error 1004: AutoFilter method of Range Class
Failed"

When I debug the line that it doesn't like is:
" .Range("A1").AutoFilter Field:=8, Criteria1:="CAD" "


Here's my macro..

Sub DeleteBlankRows()
'Assumes the list has a heading.
With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=8, Criteria1:="CAD"
.Range("A1").CurrentRegion.Offset(1,
0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With

With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=9, Criteria1:="TH"
.Range("A1").CurrentRegion.Offset(1,
0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With

With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=11,
Criteria1:="Financial"
.Range("A1").CurrentRegion.Offset(1,
0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With

With ActiveSheet
If .AutoFilterMode = False Then .Cells(1, 1).AutoFilter
.Range("A1").AutoFilter Field:=14,
Criteria1:="SENR"
.Range("A1").CurrentRegion.Offset(1,
0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub


Can you please help?
 
G

Gautam

Thank you very much Ron! The site was very helpful and had the answer
I needed. If anyone in the future needs something simlair here's the
modified code(it just deletes the entire row from specific columns
that have the word "cad" in there):

Sub DeleteRows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the A column in this example
With .Cells(Lrow, "H")

If Not IsError(.Value) Then

If .Value = "CAD" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

End If
End With

With .Cells(Lrow, "I")

If Not IsError(.Value) Then

If .Value = "CAD" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

End If
End With

With .Cells(Lrow, "K")

If Not IsError(.Value) Then

If .Value = "CAD" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

End If
End With

With .Cells(Lrow, "N")

If Not IsError(.Value) Then

If .Value = "CAD" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

End If
End With
Next Lrow

End With

'Delete all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Delete

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

End Sub
 
G

Gautam

hmm there's something weird going on. I don't know programming very
well so I am sure this is a dumb mistake..


Here's my modified version of the script that i modified to use on the
actual file:

Sub DeleteRows()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim rng As Range

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

'We check the values in the A column in this example
With .Cells(Lrow, "AQ")

If Not IsError(.Value) Then

If .Value = "#N/A N.A." Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

End If
End With
'Delete columns within AR
With .Cells(Lrow, "AR")

If Not IsError(.Value) Then

If .Value = "#N/A N.A." Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

If .Value = "#N/A N Ap" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If


End If
End With

'Delete Rows on column AS
With .Cells(Lrow, "AS")

If Not IsError(.Value) Then

If .Value = "#N/A N.A." Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

If .Value = "#N/A N Ap" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If


End If
End With

'Delete Two negatitive outputs on AT
With .Cells(Lrow, "AT")

If Not IsError(.Value) Then

If .Value = "#N/A N.A." Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

If .Value = "#N/A N Ap" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If


End If
End With






Next Lrow

End With

'Delete all rows in one time
If Not rng Is Nothing Then rng.EntireRow.Delete

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

End Sub




** But I keep getting an error when I run it:
"cannot use that command on overlapping selections" error 1004



But when I cut the lines below, the script runs fine..then when I past
this data back in then it also works..but when I use the entire script
together I keep getting the above error. PLEASE help!!


'Delete Rows on column AS
With .Cells(Lrow, "AS")

If Not IsError(.Value) Then

If .Value = "#N/A N.A." Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

If .Value = "#N/A N Ap" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If


End If
End With

'Delete Two negatitive outputs on AT
With .Cells(Lrow, "AT")

If Not IsError(.Value) Then

If .Value = "#N/A N.A." Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If

If .Value = "#N/A N Ap" Then
'This will delete each row with the Value
"ron"
'in Column A, case sensitive.

If rng Is Nothing Then
Set rng = .Cells
Else
Set rng = Application.Union(rng, .Cells)
End If
End If


End If
End With
 
D

Dave Peterson

Try replacing the problem line:
If Not rng Is Nothing Then rng.EntireRow.Delete

with this:
If Not rng Is Nothing Then
Set rng = Intersect(rng.Parent.Columns(1), rng.EntireRow)
rng.EntireRow.Delete
End If
 
B

Bruno Campanini

hmm there's something weird going on. I don't know programming very
well so I am sure this is a dumb mistake..

Here's my modified version of the script that i modified to use on the
actual file:
[...]

It looks like a shakespearian play...

Let me see: you are looking at column "A" and if you find there
cells with the strings "aaa", "bbb", "ccc", etc you want to delete
the entire rows (which is crazy as you are going to change all
worksheet layout) without replacing, at the end of data, deleted
rows with blank ones (which again is crazy for the same reason).

Could you confirm?

Bruno
 
G

Gautam

That did it!

Thanks alot!

Try replacing the problem line:
If Not rng Is Nothing Then rng.EntireRow.Delete

with this:
    If Not rng Is Nothing Then
        Set rng = Intersect(rng.Parent.Columns(1), rng.EntireRow)
        rng.EntireRow.Delete
    End If




























































...

read more »- Hide quoted text -

- Show quoted text -
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

delete specific data 1
select cell rather selecting entire row 1
Change definite value to string reference for criteria 1
How to use autofilter to delete duplicate rows (2nd criteria) ? 2
Delete code 4
Need help with a "If" "Else" VB code 4
another filtering question 1
Code execution is correct but generates Error :The file you aretrying to open is in a different form 3

Top