delete rows

  • Thread starter Thread starter m
  • Start date Start date
M

m

I am working on a macro that will copy an entire row of
data onto another sheet based on a condition in the first
column. the only way i know how to do this is to copy the
entire source worksheet into a temp worksheet, tag each
row as either keep or delete, delete all of the rows that
i dont want, and then copy the remainder to destination
worksheet. here is what i am trying to use to delete the
rows:

For rwIndex = 1 To 1000
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
Rows(rwIndex).Delete
End If
End With
Next rwIndex

however, this formula does not work. i can get it to hide
the rows (hidden = ture) but not delete. any ideas? thanks
 
m

two ways:

Sub Test2()
' start from the bottom
Dim LastRow As Long
Dim rwIndex As Long
LastRow = Range("A65536").End(xlUp).Row
For rwIndex = LastRow To 1 Step -1
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
Rows(rwIndex).Delete
End If
End With
Next 'rwIndex
End Sub

Sub Test3()
Dim LastRow As Long
Dim rwIndex As Long
Dim RangeToDelete As Range
LastRow = Range("A65536").End(xlUp).Row
For rwIndex = 1 To LastRow
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
If RangeToDelete Is Nothing Then
Set RangeToDelete = _
ActiveSheet.Cells(rwIndex, 1)
Else
Set RangeToDelete = _
Union(ActiveSheet.Cells(rwIndex, 1), _
RangeToDelete)
End If
End If
End With
Next 'rwIndex
If Not RangeToDelete Is Nothing Then
RangeToDelete.EntireRow.Delete
End If
End Sub

Regards

Trevor
 
Couple of alternatives:

Dim destRange as Range
Set destRange = Sheets("Sheet2").Range("A1")
For rwIndex = 1 to 1000
If Cells(rwIndex, 1).Value = "X" Then
Cells(rwIndex, 1).EntireRow.Copy _
destination:= destRange
Set destRange = destRange.Offset(1, 0)
End If
Next rwIndex

Alternatively:

Public Sub CopyRows()
Application.ScreenUpdating = False
With Sheets("Sheet1")
.Rows(1).Insert
.Range("A1").Value = "temp"
.Range("A1").AutoFilter _
Field:=1, _
Criteria1:="X"
On Error Resume Next
Intersect(.Range("2:65536"), .UsedRange).SpecialCells( _
xlCellTypeVisible).Copy _
Destination:=Sheets("Sheet2").Range("A1")
On Error GoTo 0
Selection.AutoFilter
.Rows(1).Delete
End With
Application.ScreenUpdating = True
End Sub
 
If you use Do While instead of For...Next loop,
you can use your own code adding just one more statements
like:
i=1
Do While i<=1000
With ActiveSheet.Cells(rwIndex, 1)
If ((.Value) = "X") Then
Rows(rwIndex).Delete
else
i=i+1
End If
End With
Loop

The use of For...Next updates the increments on every
loop "jumping" over the row immediately after the just
deleted. The code above will make sure that all the rows
are examined.
 
Back
Top