|
Delete Row if value <
I have the following code that should delete any row that doesn't have the
value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
It's case sensitive, so "DEFECTIVE" is < "Defective".
use if lcase(c.value)="defective" Sam "Aaron" wrote: I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Hi Aaron
Check out this page for example code http://www.rondebruin.nl/delete.htm When you delete rows always start at the bottom and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Sorry that is just how I wrote in in the message, I have the "DEFECTIVE" in
the code matching the exact way the cell value is showing. "Sam Wilson" wrote: It's case sensitive, so "DEFECTIVE" is < "Defective". use if lcase(c.value)="defective" Sam "Aaron" wrote: I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Worked great except it deleted the header row??
How can I change it? "Ron de Bruin" wrote: Hi Aaron Check out this page for example code http://www.rondebruin.nl/delete.htm When you delete rows always start at the bottom and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Show us the macro you use
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... Worked great except it deleted the header row?? How can I change it? "Ron de Bruin" wrote: Hi Aaron Check out this page for example code http://www.rondebruin.nl/delete.htm When you delete rows always start at the bottom and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Sub Loop_Example()
Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "C") If Not IsError(.Value) Then If .Value < "HP DEFECTIVE" Then .EntireRow.delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub "Ron de Bruin" wrote: Show us the macro you use -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... Worked great except it deleted the header row?? How can I change it? "Ron de Bruin" wrote: Hi Aaron Check out this page for example code http://www.rondebruin.nl/delete.htm When you delete rows always start at the bottom and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Hi Aaron
Change Firstrow = .UsedRange.Cells(1).Row To Firstrow = 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "C") If Not IsError(.Value) Then If .Value < "HP DEFECTIVE" Then .EntireRow.delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub "Ron de Bruin" wrote: Show us the macro you use -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... Worked great except it deleted the header row?? How can I change it? "Ron de Bruin" wrote: Hi Aaron Check out this page for example code http://www.rondebruin.nl/delete.htm When you delete rows always start at the bottom and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
Delete Row if value <
Worked prefect! Thanks!
"Ron de Bruin" wrote: Hi Aaron Change Firstrow = .UsedRange.Cells(1).Row To Firstrow = 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With 'We use the ActiveSheet but you can replace this with 'Sheets("MySheet")if you want With ActiveSheet 'We select the sheet so we can change the window view .Select 'If you are in Page Break Preview Or Page Layout view go 'back to normal view, we do this for speed ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView 'Turn off Page Breaks, we do this for speed .DisplayPageBreaks = False 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "C") If Not IsError(.Value) Then If .Value < "HP DEFECTIVE" Then .EntireRow.delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow End With ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub "Ron de Bruin" wrote: Show us the macro you use -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... Worked great except it deleted the header row?? How can I change it? "Ron de Bruin" wrote: Hi Aaron Check out this page for example code http://www.rondebruin.nl/delete.htm When you delete rows always start at the bottom and go up -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Aaron" wrote in message ... I have the following code that should delete any row that doesn't have the value of Defective in my range but it is deleting everything. Please help. lastrow = Cells(Rows.Count, "A").End(xlUp).Row For Each c In ActiveSheet.Range("C2:C" & lastrow) If c.Value = "DEFECTIVE" Then Else Selection.EntireRow.delete End If Next Thanks in advance, Aaron |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com