Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
For Rick Rothstein | Excel Programming | |||
IsNumber (in Rick Rothstein's example) | Excel Programming | |||
Rick Rothstein | Excel Programming | |||
Question for Rick Rothstein | Excel Discussion (Misc queries) | |||
answer to Rick | Excel Programming |