Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help deleting a row
This macro deletes all rows that on column "F" have the word "Take Out"
It works fine but, it takes to long, and now I need the macro to delete this and also go to column "G" and delete all that are not "allowance". I need this fast, the current macro take about 3 to 4 minutes 'Borra los que no sean Customer Validos Columns("F:F").Select With Selection Set c = .Find("Take Out", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set d = c.Offset(-1, 0) c.EntireRow.Delete Set c = .FindNext(d) Loop While Not c Is Nothing End If End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help deleting a row
Make one big range out of all of the found cells and the delete the entire
range all at onece similar to this... dim rngAll as range Columns("F:F").Select With Selection Set c = .Find("Take Out", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address set rngAll = c Do set rngAll = Union(c, rngAll) Set c = .FindNext(c) Loop While c.address < firstAddress rngall.delete End If End With (untested but it should be close) -- HTH... Jim Thomlinson "MESTRELLA29" wrote: This macro deletes all rows that on column "F" have the word "Take Out" It works fine but, it takes to long, and now I need the macro to delete this and also go to column "G" and delete all that are not "allowance". I need this fast, the current macro take about 3 to 4 minutes 'Borra los que no sean Customer Validos Columns("F:F").Select With Selection Set c = .Find("Take Out", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set d = c.Offset(-1, 0) c.EntireRow.Delete Set c = .FindNext(d) Loop While Not c Is Nothing End If End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help deleting a row
It is much better to sort your data first based on your deletion criteria. Excel deletes blocks of
rows much more quickly than individual rows, which you will find out when you have a lot of rows, and a lot of rows to be deleted interspersed. For your problem, try the code below. HTH, Bernie MS Excel MVP Sub DeleteTakeOutAndAllowance() Dim myRows As Long Range("A1").EntireColumn.Inser*t Range("A1").FormulaR1C1 = _ "=IF(RC[6]=""Take Out"",""Trash"",""Keep"*")" myRows = ActiveSheet.Range("G65536").En*d(xlUp).Row Range("A1").Copy Range("A1:A" & myRows) With Range(Range("A1"), Range("A1").End(xlDown)) .Copy .PasteSpecial Paste:=xlValues End With Cells.Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Columns("A:A").Find(What:="Tra*sh", After:=Range("A1")).Select Range(Selection, Selection.End(xlDown)).EntireR*ow.Delete Range("A1").FormulaR1C1 = _ "=IF(RC[7]<""Allowance"",""Trash"",""Keep"*")" myRows = ActiveSheet.Range("H65536").En*d(xlUp).Row Range("A1").Copy Range("A1:A" & myRows) With Range(Range("A1"), Range("A1").End(xlDown)) .Copy .PasteSpecial Paste:=xlValues End With Cells.Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending Columns("A:A").Find(What:="Tra*sh", After:=Range("A1")).Select Range(Selection, Selection.End(xlDown)).EntireR*ow.Delete Range("A1").EntireColumn.Delet*e End Sub "MESTRELLA29" wrote in message ... This macro deletes all rows that on column "F" have the word "Take Out" It works fine but, it takes to long, and now I need the macro to delete this and also go to column "G" and delete all that are not "allowance". I need this fast, the current macro take about 3 to 4 minutes 'Borra los que no sean Customer Validos Columns("F:F").Select With Selection Set c = .Find("Take Out", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set d = c.Offset(-1, 0) c.EntireRow.Delete Set c = .FindNext(d) Loop While Not c Is Nothing End If End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help deleting a row
Sorry I noticed a goof in my coding...
rngall.delete should be rngall.entirerow.delete Sorry -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Make one big range out of all of the found cells and the delete the entire range all at onece similar to this... dim rngAll as range Columns("F:F").Select With Selection Set c = .Find("Take Out", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address set rngAll = c Do set rngAll = Union(c, rngAll) Set c = .FindNext(c) Loop While c.address < firstAddress rngall.delete End If End With (untested but it should be close) -- HTH... Jim Thomlinson "MESTRELLA29" wrote: This macro deletes all rows that on column "F" have the word "Take Out" It works fine but, it takes to long, and now I need the macro to delete this and also go to column "G" and delete all that are not "allowance". I need this fast, the current macro take about 3 to 4 minutes 'Borra los que no sean Customer Validos Columns("F:F").Select With Selection Set c = .Find("Take Out", LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Set d = c.Offset(-1, 0) c.EntireRow.Delete Set c = .FindNext(d) Loop While Not c Is Nothing End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting the text without deleting the formula | Excel Worksheet Functions | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
how prevent formula in cell from deleting when deleting value???? | New Users to Excel | |||
Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming |