code for deleting rows

  • Thread starter Thread starter Jack Sons
  • Start date Start date
J

Jack Sons

Hi all,

In my workbook "ABC" I have sheet "XYZ" with some 200 columns and some 1000
rows.

I want to delete all rows (a few hundred) that have an "x" in column DA.
With a do loop I can get it done, but I think there are much more clever
ways.

Please show me cleverer code, if possible more than one way, will be
instructive for me.

Jack Sons
The Netherlands
 
Hi Jack,

This code does loop, but only deletes it all at the end

Sub DeleteRows()
Dim cLastRow As Long
Dim i As Long
Dim rng As Range

cLastRow = Cells(Rows.Count, "DA").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "DA") = "x" Then
If rng Is Nothing Then
Set rng = Cells(i, "DA").EntireRow
Else
Set rng = Union(rng, Cells(i, "A").EntireRow)
End If
End If
Next i

If Not rng Is Nothing Then
rng.Delete
End If

End Sub


This version does no looping, using autofilter

Sub DeleteRows()
Range("A1").EntireRow.Insert
Range("A1").FormulaR1C1 = "Test"
Columns("DA:DA").AutoFilter Field:=1, Criteria1:="x"
Rows().SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Thanks.

I thought of something like this (derived from an answer of - I think - Dana
DeLouis to a posting in April 2001 in this NG)

Sub WegRijMetX()

Worksheets("XYZ").Columns("DA:DA").SpecialCells(xlCellTypeConstants,
xlTextValues).EntireRow.Delete

End Sub

and it seems to work, but I do not see all possible implications or
complications. Can I be sure that the result is the same as with your code?
I am very cautious not to wreck my file.

Jack.
 
Jack,

You are right to be careful. Dana's code will delete rows with more than
just an 'x' in column DA (SpecialCells(xlCellTypeConstants,xlTextValues) as
you specified, so it may well delete rows that you did not want deleted.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,

Yes, but if I'm sure DA-cells contain either x or nothing, than it will be
OK?
Is there absolutely no specialcells-like approach possible that is focused
on, for example, x in stead of all text content (text of any kind)?

Jack.
 
Jack Sons said:
Bob,

Yes, but if I'm sure DA-cells contain either x or nothing, than it will be
OK?
Is there absolutely no specialcells-like approach possible that is focused
on, for example, x in stead of all text content (text of any kind)?

Jack.
Click to expand...

Jack,

If you are sure it will only be x or nothing, you should be okay as you say.

There is no SpecialCells for a value, just think about how many possible
options there might be. This is really what Autofilter is for. That second
piece of code I gave you is very simple, and can adapt to any value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I think Dana once posted a routine that checked for errors and then if it found
none, it would do a mass change ("X" to #n/a). Then he could use .specialcells
for that.

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myTestRng As Range

Set myRng = Worksheets("sheet1").Range("DA:DA")

Set myTestRng = Nothing
On Error Resume Next
Set myTestRng = myRng.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myTestRng Is Nothing Then
myRng.Replace what:="X", replacement:="#n/a", lookat:=xlWhole, _
searchorder:=xlByRows, MatchCase:=False

On Error Resume Next
Set myTestRng = myRng.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myTestRng Is Nothing Then
'no X's found
Else
myTestRng.EntireRow.Delete
End If
Else
MsgBox "do it by some other method"

End If

End Sub


Another option:
Apply Data|filter|autofilter. Filter to show the X's and delete those visible
rows.
 
Dave,

Thanks, nice code, will try.
Apply Data|filter|autofilter. Filter to show the X's and delete those
Click to expand...
visible
Some 200 rows (> > I want to delete all rows (a few hundred) that have an
"x" in column DA), would be very laborious because every row must be deleted
separately in order to avoid deleting non visible rows between every two
rows adjacent in the filtered output but with non consecutive rownumbers
(hope you understand what I mean).

Jack.
 
You can delete just the visible rows.

If you do it once by hand, you'll see that this is the normal behavior.

(but try it against a test copy or close without saving if you're unsure.)

Option Explicit
Sub testme()

With ActiveSheet
If .AutoFilterMode = False Then
MsgBox "Please apply a filter"
ElseIf .FilterMode = False Then
MsgBox "You haven't filtered anything"
ElseIf ActiveSheet.AutoFilter.Range.Columns(1) _
.Cells.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
MsgBox "Nothing to delete"
Else
With .AutoFilter.Range
.Offset(1, 0).Resize(.Rows.Count - 1) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End If
End With

End Sub

This last part:

With .AutoFilter.Range
.Offset(1, 0).Resize(.Rows.Count - 1) _
.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
 
Back
Top