Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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 "m" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
Thank you both for your help!!
-----Original Message----- 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows
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. -----Original Message----- 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
How to Delete empty rows in excel in b/w rows with values | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
delete empty rows between rows with text | Excel Discussion (Misc queries) |