You gave somebody this code, which works perfectly, however I
would like to delete te "closed" row instead of hiding it. Despite my
efforts I only manage to delete one row at a time. Obviously I’m
doing something wrong.
As always - thanks in advance.

Sub findtextcopyandhide()
With Worksheets("sheet15").Range("a1:a22")
Set c = .Find(What:="closed", After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
On Error Resume Next
Do
With Sheets("sheet6")
lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
Rows(c.Row).Copy .Rows(lr)
Rows(c.Row).Hidden = True <----- Delete the row
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address <> firstAddress
End If
End With

--
HJN

When deleting rows it's best to work from the bottom of the range up.

Untested...

Const SHEET_NAME as string = "sheet15"
Const RNG_SRCH as string = "A1:A22"
Const VAL_CLOSED as string="CLOSED"
dim x as long, rngSearch as range, c as Range

set rngSearch = Worksheets(SHEET_NAME).Range(RNG_SRCH)
for x = rngSearch.cells.count to 1 step-1
set c = rngSearch.Cells(x)
if ucase(c.value) = VAL_CLOSED then c.entirerow.delete
next x


Tim





"Hennie Neuhoff" <(E-Mail Removed)> wrote in message
news:19ED03BB-2397-4160-B0F0-(E-Mail Removed)...
> You gave somebody this code, which works perfectly, however I
> would like to delete te "closed" row instead of hiding it. Despite my
> efforts I only manage to delete one row at a time. Obviously I'm
> doing something wrong.
> As always - thanks in advance.
>
> Sub findtextcopyandhide()
> With Worksheets("sheet15").Range("a1:a22")
> Set c = .Find(What:="closed", After:=Cells(1, 1), _
> LookIn:=xlValues, LookAt:=xlWhole, _
> SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False)
>
> If Not c Is Nothing Then
> firstAddress = c.Address
> On Error Resume Next
> Do
> With Sheets("sheet6")
> lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
> Rows(c.Row).Copy .Rows(lr)
> Rows(c.Row).Hidden = True <----- Delete the row
> End With
> Set c = .FindNext(c)
> Loop While Not c Is Nothing _
> And c.Address <> firstAddress
> End If
> End With
>
> --
> HJN

From the bottom up using small range

sub delrowswithcertaintext()
for i = 22 to 1 step -1
with sheets("sheet15")
if lcase(.cells(i,"a"))="closed" then .rows(i).delete
end with
next
end sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Hennie Neuhoff" <(E-Mail Removed)> wrote in message
news:19ED03BB-2397-4160-B0F0-(E-Mail Removed)...
> You gave somebody this code, which works perfectly, however I
> would like to delete te "closed" row instead of hiding it. Despite my
> efforts I only manage to delete one row at a time. Obviously I’m
> doing something wrong.
> As always - thanks in advance.
>
> Sub findtextcopyandhide()
> With Worksheets("sheet15").Range("a1:a22")
> Set c = .Find(What:="closed", After:=Cells(1, 1), _
> LookIn:=xlValues, LookAt:=xlWhole, _
> SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False)
>
> If Not c Is Nothing Then
> firstAddress = c.Address
> On Error Resume Next
> Do
> With Sheets("sheet6")
> lr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
> Rows(c.Row).Copy .Rows(lr)
> Rows(c.Row).Hidden = True <----- Delete the row
> End With
> Set c = .FindNext(c)
> Loop While Not c Is Nothing _
> And c.Address <> firstAddress
> End If
> End With
>
> --
> HJN