MS Office Forum / Word / Programming / September 2005
Find text and delete row
|
|
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
|
|
|