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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>