Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Hi
I found code from Dave Peterson. Option Explicit Sub testme02() Dim myRng As Range Dim FoundCell As Range Dim wks As Worksheet Dim myStrings As Variant Dim iCtr As Long myStrings = Array("ISA") 'add more strings if you need Set wks = ActiveSheet With wks Set myRng = .Range("a6:a" & .Rows.Count) End With For iCtr = LBound(myStrings) To UBound(myStrings) Do With myRng Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If End With Loop Next iCtr End Sub How can I delete also n following rows. Example "my text" in A8 I like to select rows 8-15 and delete this range |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
this site might help you.
http://www.rondebruin.nl/delete.htm "Jaan" wrote: Hi I found code from Dave Peterson. Option Explicit Sub testme02() Dim myRng As Range Dim FoundCell As Range Dim wks As Worksheet Dim myStrings As Variant Dim iCtr As Long myStrings = Array("ISA") 'add more strings if you need Set wks = ActiveSheet With wks Set myRng = .Range("a6:a" & .Rows.Count) End With For iCtr = LBound(myStrings) To UBound(myStrings) Do With myRng Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If End With Loop Next iCtr End Sub How can I delete also n following rows. Example "my text" in A8 I like to select rows 8-15 and delete this range |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
In the code, Change;
FoundCell.EntireRow.Delete To rows(FoundCell.Row & ":" & FoundCell.row+7).delete -- --- HTH Roger Shaftesbury (UK) "Jaan" wrote in message ... Hi I found code from Dave Peterson. Option Explicit Sub testme02() Dim myRng As Range Dim FoundCell As Range Dim wks As Worksheet Dim myStrings As Variant Dim iCtr As Long myStrings = Array("ISA") 'add more strings if you need Set wks = ActiveSheet With wks Set myRng = .Range("a6:a" & .Rows.Count) End With For iCtr = LBound(myStrings) To UBound(myStrings) Do With myRng Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If End With Loop Next iCtr End Sub How can I delete also n following rows. Example "my text" in A8 I like to select rows 8-15 and delete this range |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows
Thank Roger, it works
"Jaan" kirjutas: Hi I found code from Dave Peterson. Option Explicit Sub testme02() Dim myRng As Range Dim FoundCell As Range Dim wks As Worksheet Dim myStrings As Variant Dim iCtr As Long myStrings = Array("ISA") 'add more strings if you need Set wks = ActiveSheet With wks Set myRng = .Range("a6:a" & .Rows.Count) End With For iCtr = LBound(myStrings) To UBound(myStrings) Do With myRng Set FoundCell = .Cells.Find(what:=myStrings(iCtr), _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then Exit Do Else FoundCell.EntireRow.Delete End If End With Loop Next iCtr End Sub How can I delete also n following rows. Example "my text" in A8 I like to select rows 8-15 and delete this range |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |