On 23 Feb, 17:11, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Since the code is triggered by the change event and the change event fires
when the row is deleted how about this to clean things up a bit more.
Additionally I have added the lcase function to account for any cpitalization
in the word "yes"...

Private Sub Worksheet_Change(ByVal Target As Range)
Call inputs(Range("C1:C30"))
End Sub

Private Sub inputs(rangeref As Range)
Dim cell As Range, frow As Long, lrow As Long
Dim i As Long
On Error GoTo ErrorHandler

Application.EnableEvents = False
frow = rangeref(1).Row
lrow = rangeref(rangeref.Count).Row
For i = lrow To frow Step -1
Set cell = rangeref.Parent.Cells(i, rangeref.Column)
If LCase(cell.Value) = "yes" Then
cell.EntireRow.Delete
End If
Next i
ErrorHandler:
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson



"Tom Ogilvy" wrote:
I think Bob was focused on the initial problem and overlooked the problem
when looping forward when deleting rows. This modification would insure all
the desired rows are deleted.

Private Sub inputs(rangeref As Range)
Dim cell as Range, frow as Long, lrow as Long
Dim i as Long
frow = rangeref(1).row
lrow = rangeref(rangeref.count).row
For i = lrow to frow step -1
set cell = rangeref.parent.cells(i, rangeref.column)
If cell.Value = "yes" Then
cell.EntireRow.Delete
End If
Next i
End Sub

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote:

In addition to Chip's response, one question and one suggestion.

Question. Why would this be triggered by Worksheet_Change. This is normally
used to work upon the cell(s) that is changed, not some group of unrelated
cells.

Suggestion. You can simplify this code down to one call and loop in the
called procedure

Private Sub Worksheet_Change(ByVal Target As Range)
Call inputs(Range("C1:C30"))
End Sub

Private Sub inputs(rangeref As Range)
Dim cell As Range
For Each cell In rangeref
If cell.Value = "yes" Then
cell.EntireRow.Delete
End If
Next cell
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"MarkHear1" wrote in message
roups.com...
I have written the following code however i get the error "Type
Mismatch" and the code "C1"
Has anybody got any suggestions as to how i can resolve this problem?

Private Sub Worksheet_Change(ByVal Target As Range)

Call inputs("C1")
Call inputs("C2")
Call inputs("C3")
Call inputs("C4")
Call inputs("C5")
Call inputs("C6")
Call inputs("C7")
Call inputs("C8")
Call inputs("C9")
Call inputs("C10")
Call inputs("C11")
Call inputs("C12")
Call inputs("C13")
Call inputs("C14")
Call inputs("C15")
Call inputs("C16")
Call inputs("C17")
Call inputs("C18")
Call inputs("C19")
Call inputs("C20")
Call inputs("C21")
Call inputs("C22")
Call inputs("C23")
Call inputs("C24")
Call inputs("C25")
Call inputs("C26")
Call inputs("C27")
Call inputs("C28")
Call inputs("C29")
Call inputs("C30")
End Sub

Private Sub inputs(rangeref As Range)
If rangeref.Value = "yes" Then
rangeref.EntireRow.Delete
End Sub

Regards,
Mark- Hide quoted text -

- Show quoted text -

thank you all for your suggestions...
I have now changed the request and if the value is "yes" i want that
row to be copied to sheet 2 can anybody help with this please?