delete rows
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
In article ,
"m" wrote:
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
|