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

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

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

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