Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / September 2005

Tip: Looking for answers? Try searching our database.

Find text and delete row

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Krakmup - 29 Sep 2005 18:38 GMT
G'Day

Is it possible to reduce the lines of code for the followng, assuming I want
to find and delete rows in a table that contain "0", "-1", "-2", "-3" in
column 1.

CODE:
If Selection.Information(wdWithInTable) = False Then Exit Sub
TargetText = "0"
For Each oRow In Selection.Tables(1).Rows
If oRow.Cells(1).Range.Text = TargetText & vbCr & Chr(7) Then oRow.Delete
Next oRow

TargetText = "-1"
For Each oRow In Selection.Tables(1).Rows
If oRow.Cells(1).Range.Text = TargetText & vbCr & Chr(7) Then oRow.Delete
Next oRow

Repeating 4 lines of code seems like a waste of space.
Thanks
Krakmup
Jean-Guy Marcil - 29 Sep 2005 21:12 GMT
Krakmup was telling us:
Krakmup nous racontait que :

> G'Day
>
[quoted text clipped - 17 lines]
> Thanks
> Krakmup

If TargetText always equals 0, -1, -2 and -3 then the most simple is to do
something like:

'_______________________________________
Dim oRow As Row

If Selection.Information(wdWithInTable) = False Then Exit Sub

With Selection.Tables(1)
   For Each oRow In .Rows
       If oRow.Cells(1).Range.Text = "0" & Chr(13) & Chr(7) Then
           oRow.Delete
       ElseIf oRow.Cells(1).Range.Text = "-1" & Chr(13) & Chr(7) Then
           oRow.Delete
       ElseIf oRow.Cells(1).Range.Text = "-2" & Chr(13) & Chr(7) Then
           oRow.Delete
       ElseIf oRow.Cells(1).Range.Text = "-3" & Chr(13) & Chr(7) Then
           oRow.Delete
       End If
   Next oRow
End With
'_______________________________________

You could have a function, but since all you are doing is deleting the row,
it is not really worth it.
Or, if TargetText is defined during the procedure, then you can have a
function, like:

'_______________________________________
Sub CallDeleteRow()

Dim TargetText As String

If Selection.Information(wdWithInTable) = False Then Exit Sub

'Some code would generate TargetText,
'here we will set it "manually"
TargetText = 0

Select Case TargetText
   Case "0", "-1", "-2", "-3"
       DeleteRow Selection.Tables(1), TargetText
End Select

End Sub
'_______________________________________

'_______________________________________
Function DeleteRow(MyTable As Table, TargetText As String)

Dim oRow As Row

With MyTable
   For Each oRow In .Rows
       If oRow.Cells(1).Range.Text = TargetText _
           & Chr(13) & Chr(7) Then oRow.Delete
   Next oRow
End With

End Function
'_______________________________________

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org 

Helmut Weber - 29 Sep 2005 21:14 GMT
Hi,

how about this one:

Dim oCll As Cell
Dim s As String
Dim l As Long
If Selection.Information(wdWithInTable) = False Then Exit Sub
Selection.Tables(1).Columns(1).Select
For Each oCll In Selection.Cells
  s = oCll.Range.Text
  s = Left(s, Len(s) - 2)
  For l = 0 To -4 Step -1 '!
     If s = CStr(l) Then '!
        oCll.Row.Delete
        Exit For
     End If
  Next
Next
Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Klaus Linke - 29 Sep 2005 21:25 GMT
Hi Krakmup,

You could do a little bit better (and faster) by putting all the Ifs into
the For Each loop.

And to avoid the repeated Ifs too, how about a Select Case:

Dim oRow as Row
' End-Of-Cell-Marker:
Dim eoc as String : eoc=vbCr & Chr(7)
For Each oRow In Selection.Tables(1).Rows
   Select Case oRow.Cells(1).Range.Text
       Case "0" & eoc
           oRow.Delete
       Case "-1" & eoc
           oRow.Delete
       Case "-2" & eoc
           oRow.Delete
       Case "-3" & eoc
           oRow.Delete
   End Select
Next oRow

oRow.Cells(1).Range.Text has only to be determined once, and it seems a bit
more readable.

Regards,
Klaus

> G'Day
>
[quoted text clipped - 18 lines]
> Thanks
> Krakmup
Greg Maxey - 29 Sep 2005 21:38 GMT
Or shorter still:

Sub Test()
Dim oRow As Row
Dim eoc As String: eoc = vbCr & Chr(7)
For Each oRow In Selection.Tables(1).Rows
 Select Case oRow.Cells(1).Range.Text
    Case "0" & eoc, "-1" & eoc, "-2" & eoc, "-3" & eoc
       oRow.Delete
   Case Else
      'Do nothing
   End Select
Next oRow
End Sub
> Hi Krakmup,
>
[quoted text clipped - 47 lines]
>> Thanks
>> Krakmup
Helmut Weber - 29 Sep 2005 22:02 GMT
Hi Greg, hi Klaus,

that wasn't fair, to omitt the check,
whether the selection is in a table. ;-)

And how about the usual question,
"What if I want to delete numbers from 0 til -12345"?

In former times (Commodore VC20, C64), there were
contests on who could write a game in 1 line,
possible (!), and the shortest code wone.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Klaus Linke - 29 Sep 2005 22:49 GMT
Hi Helmut,

> that wasn't fair, to omitt the check,
> whether the selection is in a table. ;-)

Who said we're playing fair here, now? I didn't get the memo <g>

> And how about the usual question,
> "What if I want to delete numbers from 0 til -12345"?

Dim oRow As Row
Dim strCell As String
For Each oRow In Selection.Tables(1).Rows
 strCell = oRow.Cells(1).Range.Text
 strCell = Replace(strCell, vbCr & Chr(7), "")
   Select Case Val(strCell)
       Case -12345 To 0
           oRow.Delete
   End Select
Next oRow

> In former times (Commodore VC20, C64), there were
> contests on who could write a game in 1 line,
> possible (!), and the shortest code wone.

Sounds fun... I enjoy to optimize for speed, even if it sometimes takes
longer to do so than is saved later on.

:-)  Klaus
Helmut Weber - 29 Sep 2005 22:57 GMT
Hi Klaus,

You won.

> Case -12345 To 0

Didn't know that, but I know now.

Thank you, and happy coding.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Tony Jollans - 29 Sep 2005 21:43 GMT
Why not a single Case statement to simplify it even more instead of
repeating the action statement four times?

Dim oRow As Row
' End-Of-Cell-Marker:
Dim eoc As String: eoc = vbCr & Chr(7)
For Each oRow In Selection.Tables(1).Rows
   Select Case oRow.Cells(1).Range.Text
       Case "0" & eoc, _
                "-1" & eoc, _
                "-2" & eoc, _
                "-3" & eoc
           oRow.Delete
   End Select
Next oRow

Enjoy,
Tony

> Hi Krakmup,
>
[quoted text clipped - 47 lines]
> > Thanks
> > Krakmup

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.