Delete Entire Row

[K]

Hi all, I have macro to delete Entire Rows the one have "X" in column
A (see macro below).

Sub DelRow()
Dim c As Range
For Each c In Range("A1:A500").Cells
If c.Value = "X" Then
c.EntireRow.Delete
End If
Next c
End Sub

The problem I am having that macro don’t delete all those rows the one
have character "X" in column A. I have to run macro more than one
time to delete all rows. Please can any friend can help that how can
I achive my goal just running macro once.

 

[Mike H]

Hi,

You have to loop backwards through the range

Sub DelRow()
Dim c As Range
For x = 500 To 1 Step -1
If Cells(x, 1).Value = "X" Then
Rows(x).EntireRow.Delete
End If
Next
End Sub

Mike

 

[PaulD]

When deleting rows, it is better to start from the bottom and work up
Sub DelRow()
Dim c As Integer
For c = 500 to 1 step -1
If cells(c,1).Value = "X" Then
activesheet.rows(c).delete
End If
Next c
End Sub


Hi all, I have macro to delete Entire Rows the one have "X" in column
A (see macro below).

Sub DelRow()
Dim c As Range
For Each c In Range("A1:A500").Cells
If c.Value = "X" Then
c.EntireRow.Delete
End If
Next c
End Sub

The problem I am having that macro don’t delete all those rows the one
have character "X" in column A. I have to run macro more than one
time to delete all rows. Please can any friend can help that how can
I achive my goal just running macro once.

 

[Mike H]

Hi,

Here's a method of going forward through the range doing the same thing on
the used range of column A instead of using a fixed A1 - A500

Sub Please_Delete_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myRange = Range("A1:A" & lastrow)
For Each c In myRange
If c.Value = "X" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike

 

[K]

Hi,

Here's a method of going forward through the range doing the same thing on
the used range of column A instead of using a fixed A1 - A500

Sub Please_Delete_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set myRange = Range("A1:A" & lastrow)
For Each c In myRange
    If c.Value = "X" Then
        If copyrange Is Nothing Then
                Set copyrange = c.EntireRow
        Else
                Set copyrange = Union(copyrange, c.EntireRow)
        End If
    End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub

Mike







- Show quoted text -

Thats great. Thanks guys