Copy Entire Row - Rick Rothstein ?
 

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 Im
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

Copy Entire Row - Rick Rothstein ?
 

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" wrote in message
...
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

Copy Entire Row - Rick Rothstein ?
 

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

"Hennie Neuhoff" wrote in message
...
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 Im
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