HTML parsing with VBA
 

hello, I am trying to modify a recorded macro that replaces an HTML tag

with "". The macro I recorded is below. I want to replace the 7 in the
Rows parameter with a counter, i, and use a For ..Next statement to
loop through each row in the worksheet. However, this is giving me an
app error. Does anyone know how I can do this? Thanks.

------ Original Macro -------
Sub CleanHTML()


' CleanHTML Macro
' Macro recorded 4/17/2006 by Mark Oium


Rows("7:7").Select
Selection.Replace What:="<*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False
End Sub


------ Modified Macro ------
Sub CleanHTML()


Dim i as Integer


'CleanHTML Macro
'Macro recorded 4/17/2006 by Mark Oium


For i = 1 To 1396
Rows("i:i").Select
Selection.Replace What:="<*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False
Next i
End Sub

HTML parsing with VBA
 

For i = 1 To 1396
Rows("i:i").Select
Selection.Replace What:="<*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False
Next i
End Sub

For i = 1 To 1396
Rows(i).Replace What:= etc.
Next i

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

HTML parsing with VBA
 

Thhanks Dick. That worked without any errors. But now it seems as
though the macro is not searching the entire row. The file has 90
columns and up to 1400 rows. Many of the cells contain several HTML
tags and not all are being replaced with "". Also, some tags are
preceded by a few spaces, these are not being replaced either. Do you
have any advice? Thanks.

HTML parsing with VBA
 

wrote:
Thhanks Dick. That worked without any errors. But now it seems as
though the macro is not searching the entire row. The file has 90
columns and up to 1400 rows. Many of the cells contain several HTML
tags and not all are being replaced with "". Also, some tags are
preceded by a few spaces, these are not being replaced either. Do you
have any advice? Thanks.

This worked for me

Sheet1.Rows("1:52").Replace "<*", "", xlPart, xlByRows, False, , False,
False

replace the 52 with whichever number of rows you want.

I don't know why it wouldn't replace all the tags. Maybe you can post an
example of one it's not replacing. It won't replace spaces unless you tell
it to. You could repeat the above line three times to catch when there are
two preceding spaces.

Sheet1.Rows("1:52").Replace " <*", "", xlPart, xlByRows, False, , False,
False
Sheet1.Rows("1:52").Replace " <*", "", xlPart, xlByRows, False, , False,
False
Sheet1.Rows("1:52").Replace "<*", "", xlPart, xlByRows, False, , False,
False

The first one looks for two spaces, then one, then none.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com