Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row based on a value in a column
Hi there!
Over the past few days I've been working on a workflow system in Excel. One of the features that was wanted is a clear down function, which will automatically delete all off the rows with "Y" in Sheets("Work") columns H and I. The only thing is I can't seem to get any form of my code to work... The majority of snippets that require listing values in certain rows I just just the for each cell in rows, if cell.value2 = data then object.additem... etc. Could someone help me with this code and let me know how I can make it workable? Private Sub run_cleardown_Click() Dim data As String data = "Y" For Each cell In Sheets("Work").Range("H:H") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If Next For Each cell In Sheets("Work").Range("I:I") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If End Sub Thanks! Kai |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row based on a value in a column
Try this
Private Sub run_cleardown_Click() Dim data As String Dim iLastrow As Long Dim iLastrow1 As Long Dim iLastrow2 As Long Dim i As Long data = "Y" With Sheets("Work") iLastrow1 = .Cells(Rows.Count, "H").End(xlUp).Row iLastrow2 = .Cells(Rows.Count, "I").End(xlUp).Row iLastrow = iLastrow1 If iLastrow2 iLastrow Then iLastrow = iLastrow2 For i = iLastrow To 1 Step -1 If .Cells(i, "H").Value = data Or _ .Cells(i, "I").Value = data Then .Rows(i).Delete End If Next i End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Kai" wrote in message . .. Hi there! Over the past few days I've been working on a workflow system in Excel. One of the features that was wanted is a clear down function, which will automatically delete all off the rows with "Y" in Sheets("Work") columns H and I. The only thing is I can't seem to get any form of my code to work... The majority of snippets that require listing values in certain rows I just just the for each cell in rows, if cell.value2 = data then object.additem... etc. Could someone help me with this code and let me know how I can make it workable? Private Sub run_cleardown_Click() Dim data As String data = "Y" For Each cell In Sheets("Work").Range("H:H") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If Next For Each cell In Sheets("Work").Range("I:I") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If End Sub Thanks! Kai |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row based on a value in a column
You need this corrections:
Private Sub run_cleardown_Click() Dim data As String data = "Y" For Each Cell In Sheets("Work").Range("H:H", "I:I") If Cell.Value2 = data Then Cell.EntireRow.Delete Else End If Next End Sub Note: make sure you type the value in column H and I as capital letter. Good luck. "Kai" wrote in message . .. Hi there! Over the past few days I've been working on a workflow system in Excel. One of the features that was wanted is a clear down function, which will automatically delete all off the rows with "Y" in Sheets("Work") columns H and I. The only thing is I can't seem to get any form of my code to work... The majority of snippets that require listing values in certain rows I just just the for each cell in rows, if cell.value2 = data then object.additem... etc. Could someone help me with this code and let me know how I can make it workable? Private Sub run_cleardown_Click() Dim data As String data = "Y" For Each cell In Sheets("Work").Range("H:H") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If Next For Each cell In Sheets("Work").Range("I:I") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If End Sub Thanks! Kai |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a row based on a value in a column
Kai,
The main problem is that you seem to be using EntireRow as an object rather than as a property. To fix, insert cell. (the object) at the beginning, and get rid of cell.Value2 at the end, in those two lines of code. Thus, they should be changed to: cell.EntireRow.Delete cell is the object EntireRow is a property of that object Delete is a method that operates on the object Also, you should dimension cell (Dim cell as Range) at the top, and you need to add a Next at the end of your second loop. You can also kill the Else's unless you plan to add code for alternative action if data < "Y" The code to achieve your objective could be done more efficiently, but these fixes should make it work. HTH, Roy "Kai" wrote in message . .. Hi there! Over the past few days I've been working on a workflow system in Excel. One of the features that was wanted is a clear down function, which will automatically delete all off the rows with "Y" in Sheets("Work") columns H and I. The only thing is I can't seem to get any form of my code to work... The majority of snippets that require listing values in certain rows I just just the for each cell in rows, if cell.value2 = data then object.additem... etc. Could someone help me with this code and let me know how I can make it workable? Private Sub run_cleardown_Click() Dim data As String data = "Y" For Each cell In Sheets("Work").Range("H:H") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If Next For Each cell In Sheets("Work").Range("I:I") If cell.Value2 = data Then EntireRow.Delete cell.Value2 Else End If End Sub Thanks! Kai |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting a row of data based upon the value in column D | Excel Programming | |||
Deleting rows based upon the value in column D | Excel Programming | |||
Deleting a row based on the value in column A | Excel Programming | |||
Deleting a column based on a value | Excel Programming |