Remove last <li> entry from cells

[Easton King]

I have a large xls doc and some cells have entries that end with
<li>. I want to remove only this last instance of <li> and only when
it is the last thing in the particular cell.
For example:
<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>

In this case, I want to remove only the last instance of the <li>

I have ASAP utils also, but can't seem to figure the logic to make
this happen.

TIA for any help you may be able to provide.

 

[Don Guillett]

Try this copied exactly as shown and changing column I to suit

Sub removelastitem()
For i = Cells(Rows.Count, "i").End(xlUp).Row To 2 Step -1
If Len(Cells(i, "i")) > 4 And _
Right(Cells(i, "i"), 4) = "" < lid > "" Then
Rows(i).Delete
Exit For
End If
Next i
End Sub

 

[Don Guillett]

lid should be li. Somehow it got changed in the transmission (probably
winmail spell check)

 

[Don Guillett]

It was pointed out correctly to me by Sandy that this should have had only
one set of quotes.
That is correct IF I hadn't copied the text below which had the / in the
data giving</li> on my reader. When I go back and type in <li> then it
should be "<li>" but with what I copied excel changed to ""< li >"".??????
Try it both ways and see what you get.
Again, I do not mind being questioned or criticized in the post. Harlan
delights in finding my errors.

Sub removelastitem()
For i = Cells(Rows.Count, "i").End(xlUp).Row To 2 Step -1
If Len(Cells(i, "i")) > 4 And _

Right(Cells(i, "i"), 4) = "<li>" Then ' <li>
'Right(Cells(i, "i"), 4) = "" < li > "" Then ' </li> as tested

Rows(i).Delete
Exit For
End If
Next i
End Sub

 

[Sandy Mann]

mmmmm.....

Its a matter of interpretation - not my strong suit usually. My take is
that when the OP said:

In this case, I want to remove only the last instance of the <li>

they wanted the </li> left alone.

Doing a Google search for <li> and </li> it seems, (I didn't know), that
they are HTML tags for lists as in:

<li> Item 1 </li>
<li> Item 2 </li>

I assumed therefore that the whole of the data provided by the OP was in one
cll and the extra lone <li> 's were what the OP wanted removed.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Assuming that all the data given was in one cell then to remove the extra
HTML Tags try:

Sub DeleteTag()
For Each cell In Selection
cell.Value = Application.Substitute(cell.Value, "<li>" & Chr(10),
"")
Next cell
End Sub

Perhaps the OP will enlighten us as to what he/she wants but in any case I'm
off to bed.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

No that left the last <li> in place try:

Sub DeleteTag()
For Each cell In Selection
cell.Value = _
Application.Substitute(cell.Value, "<li>" & Chr(10), "")

If Right(cell.Value, 4) = "<li>" Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 4)
Next cell
End Sub


Now I really am off to bed!
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

It's no good I forgot to test Don's code and I wouldn't be able to get to
sleep for thinking about it.

'Right(Cells(i, "i"), 4) = "" < li > "" Then ' </li> as tested

I find that I can't enter the </li> sequence with double quotes without VBA
telling me that it expects an expression, (but it will however accept it
with single" quotes but then it does nothing). I also find that ""<li>"" in
that line removes *any* line except the single <li> lines try running your
code repeatedly on:

<li>
Blah blah blah</li>
Don
<li>
Blah blah blah</li>
Sandy
<li>
Blah blah blah</li>
<li>


Mind empty - practicing meditation - at peace with the world -and off to
bed.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Here is my take on the question of double quotes:

The line:

If Len(Cells(i, "i")) > 4 And _
Right(Cells(i, "i"), 4) = "<li>" Then ' <li>

changes to:

If Len(Cells(i, "i")) > 4 And _
Right(Cells(i, "i"), 4) = "" < li > "" Then ' <li>

when additional quotes are added. Why does VBA add the extra spaces and
what difference do they make?

Well, I think that VBA is interpreting * "" < li * as: An empty string is
smaller than an undeclared variable called li with a zero value, which is
TRUE.

VBA substitutes TRUE and the line then reads:
Right(Cells(i, "i"), 4) = TRUE > "" Then ' <li>

Where TRUE, which XL equates of a value of 1, is greater then an empty
string with a value of zero. This again is TRUE, so this line therefore
passes for *any* value in the cell with a length longer than 4

This is borne out by the fact that if you put Option Explicit at the top of
the code, (as I have read here always a good thing to do), and DIM the
variable i, VBA objects to the undeclared variable li.

What I don't understand, and perhaps someone who has a greater understanding
of VBA them me could explain, is, if the above is TRUE, then why does:

Right(Cells(i, "i"), 4) = TRUE Then ' <li>

not work or does VBA have to coerce the TRUE value itself?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

A reiteration of what I did (Vista HP, xl2003 up to date)

I copied the list below into col I
I copied the LAST item which was <li> and got no deletion using "<li>"
I copied the next to last item and removed the preceding text to get "</li>"
Somehow, excel changed to ""< li >""
But NOW, I can't replicate.???? Hopefully the OP will tell us the desired
deletion

 

[Easton King]

A reiteration of what I did (Vista HP, xl2003 up to date)

I copied the list below into col I
I copied the LAST item which was <li> and got no deletion using "<li>"
I copied the next to last item and removed the preceding text to get "</li>"
Somehow, excel changed to ""< li >""
But NOW, I can't replicate.???? Hopefully the OP will tell us the desired
deletion

The intended output for this issue would be:

<li>
Blah blah blah</li>
Don
<li>
Blah blah blah</li>
Sandy
<li>
Blah blah blah</li>

 

[Don Guillett]

Re state your desired outcome. There is confusion about the / within the
<li>

 

[Sandy Mann]

Is the data that you gave:

<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>
Blah blah blah</li>
<li>

all in the one cell? I am going to assume that it is and that the final <li>
may or may not have a line break after it.

Copy the code below and then press the Alt and the function key F11 together
to take you into VBA, then select Insert > Module and in the white module
sheet paste this code into the module:

Sub RemoveIt()
For Each cell In Selection
If Right(cell, 4) = "<li>" Then
cell.Value = Left(cell.Value, Len(cell) - 4)
End If
If Right(cell, 5) = "<li>" & Chr(10) Then
cell.Value = Left(cell.Value, Len(cell) - 5)
End If
Next cell
End Sub

Now in the worksheet select the cells that you want to remove the <li> from
and select Tools > Macro > Macros and click Ok.

This will remove the last <li> from the cells whether or not there is a line
break after it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

It is very rude to ask a question that others are trying to answer for you
and for you to ignore their requests for information.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Re state your desired outcome. There is confusion about the / within
the <li>

 

[Easton King]

It is very rude to ask a question that others are trying to answer for you
and for you to ignore their requests for information.

I agree that this is rude. That is why I am trying to monitor this
group. I'm sorry that I was called away from the newsgroup for a
couple of hours...Your post is again asking for a re-statement that
has already been made. I do not know how to make this clearer...
The last thing that is in this cell should be </li>. I am trying to
remove the last <li> that appears in the op.

 

[Sandy Mann]

The last thing that is in this cell should be </li>. I am trying to
remove the last <li> that appears in the op.

Isn't that just what my suggested code does?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Easton King]

Isn't that just what my suggested code does?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

It does indeed. Thanks so much. Now to go back and figure out how
that worked.....
I appreciate your help and apologize for the delay in responding.
Thanks again!

 

[Sandy Mann]

You are very welcome, I'm glad that we got there in the end. <g>

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk