Macro to apply row borders

[aa2000]

Hi guys

So I have a macro which imports a text file to excel. The text files are made up of individual tables that each end with the word "END"

What I am now trying to do is get another macro running that detects every instance of "END" and then puts a continuous border along the bottom of that entire row.

My current code is

Sub EndBorder()
Set Borderrange = Range("A3:AS1000")
For Each Cell In Borderrange
If Cell.Value = "END" Then Cell.Borders(xlEdgeBottom).LineStyle = xlContinuous
Next Cell

End Sub

However this code only puts the border under the cell actually containing "END". How can I amend this to select the whole row

Thanks

 

[Weaver]

Sub EndBorder()
Set Borderrange = Range("A3:AS1000")
For Each c In Borderrange.cells
If c.Value = "END" Then c.entirerow.Borders(xlEdgeBottom).LineStyle = xlContinuous
Next c
End Sub

Best to avoid reserved words as variable names

HTH

 

[aa2000]

Thank you for the code, it worked!

Unfortunately the text files are created automatically by a measurement tool we use, and I have no control over how it displays the results.

 

[Peter_SSs]

Best to avoid reserved words as variable names

What reserved word was used?

 

[Weaver]

What reserved word was used?

is Cell not a reserved word?

Bad terminology in my case perhaps, but I still think it's better to avoid variable names that look like they might be part of the underlying language.

 

[VoG]

Cell isn't a reserved word. Cells is.

I generally use c though as it saves on typing

 

[Peter_SSs]

I'm assuming that the tables must have a varying number of columns since you are not just searching in a particular column. I also don't know how many tables there are likely to be but searching every cell seems to me to be an inefficient way to go.

Perhaps you could consider something like below. If there are, say, 50 tables this will only need to do 50 rather than 44,910 loops.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> EndBorder()<br>    <SPAN style="color:#00007F">Dim</SPAN> Borderrange <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> firstaddr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Borderrange = Range("A3:AS1000")<br>    <SPAN style="color:#00007F">With</SPAN> Borderrange<br>        <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:="END", LookIn:=xlValues, _<br>            Lookat:=xlWhole, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            firstaddr = Found.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                Found.EntireRow.Borders(xlEdgeBottom).LineStyle _<br>                    = xlContinuous<br>                <SPAN style="color:#00007F">Set</SPAN> Found = .FindNext(After:=Found)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Found.Address = firstaddr<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>