Excel VBA to know what kind of border it is

[JohnSeito]

Does any know of excel code to know what know what kind of border it is. I converted a pdf file and now the data are on excel and there are borders and it looks to me like it is border with thickness of medium but when I run a code I produced and mark X to the column next to it to show that it is that border it gave me X on all the rows and so the medium thick border is excel grid without any borders.

So if anyone could show a code example of what kind of border does an excel has then let me know. thanks.

 

[JohnSeito]

Len(a1) etc on the column where border, shows 0.

Copy paste special value of the border to another sheet, the borders are gone.

 

[mikerickson]

Copy All / Clear Formats / Paste Special Values sounds like it would get rid of the borders without having to probe them individualy.

 

[JohnSeito]

Copy All / Clear Formats / Paste Special Values sounds like it would get rid of the borders without having to probe them individualy.

yea that is what i did and after paste special value all the borders are gone. so would you say all of them are borders then? if so we need to find out why it doesn't work with xlthin, xlmedium or xlthick, and what kind of borders they are.

 

[mikerickson]

My point is that if "no borders" is the goal, then "what kind of borders" is irrelevant.

 

[JohnSeito]

My point is that if "no borders" is the goal, then "what kind of borders" is irrelevant.

no, no borders is not the goal, the goal is to mark an x to any row that has a border.

example if column a has borders, not on every row but on some due to the conversion from PDF, then my code would tell me which rows have borders. I can't do this manually by putting X because there is too many for example 10,000s or 20,000 rows.

And to mark X to any row that has a border we have to be able to identify it.

 

[mikerickson]

I don't know what kind of borders you are getting.
This code might help

Sub test()
Dim oneCell As Range
With ThisWorkbook.Sheets("sheet1").Range("C:C")
    For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        If HasSomeBorder(oneCell) Then oneCell.Offset(0, 1).Value = "X"
    Next oneCell
End With
End Sub

Function HasSomeBorder(aCell As Range) As Boolean
    With aCell.Cells(1, 1)
        HasSomeBorder = Not ((.Borders(xlEdgeTop).ColorIndex = xlNone) _
                                And (.Borders(xlEdgeBottom).ColorIndex = xlNone) _
                                And (.Borders(xlEdgeLeft).ColorIndex = xlNone) _
                                And (.Borders(xlEdgeRight).ColorIndex = xlNone))
    End With
End Function

But, if a cell has a border all around it, the cell above it also has a lower border, so the test in HasSomeBorder needs to be modified a bit.

If all the cells that you want to have have a border on all 4 sides, this will catch it (and not the above and below cells as the previous version does.)

HasSomeBorder = (.Borders(xlEdgeTop).ColorIndex <> xlNone) _
                And (.Borders(xlEdgeBottom).ColorIndex <> xlNone) _
                And (.Borders(xlEdgeLeft).ColorIndex <> xlNone) _
                And (.Borders(xlEdgeRight).ColorIndex <> xlNone)

Does this help?

 

[JohnSeito]

mikerickson

That works, so instead of say what border(thin, medium, or thick) it has, we are just saying that if it has border weather medium, thick or thin, mark x. I am only interested in seeing if it has the bottom border and I have adjusted your code to do just that.

I still wonder why when say if it has either thin, medium, or thick bottom border it doesn't work.

Thanks for the help.

This code

HasSomeBorder = Not ((.Borders(xlEdgeBottom).ColorIndex = xlNone))

and this code does the same thing.

HasSomeBorder = (.Borders(xlEdgeBottom).ColorIndex <> xlNone)

one other thing I am wondering if you know is that let say if I filter excel, can I do copy and paste special value while excel is in filter mode? I tried that and it can't be done, do you know if this is possible. I only want to do this because so I don't have to un-filter to copy and paste special value just to get rid of the formula.

 

[mikerickson]

Copy PasteSpecial Values works with AutoFiltered cells on my Excel2004.

When pasting to a filtered sheet, the columns pasted into cannot have a cell with arrows.

 

[JohnSeito]

example:

column a heading A i have
1
2
5
3
6
7
8

column b heading B I have blanks.

I filter column a as less than 5.
we see only
1
2
3

in column b i put formula after the filtered
= a2
= a3
= a5

I then want to copy and paste special value to column b while it is on filter mode to get rid of the formula. Do you know if excel can do this? I tried and it doesn't seem to work. thx

 

[mikerickson]

I can't see how to do that. A workaround might be devised that pasted into a blank column (D for example), turn off the filter and from the intermediate col to column B. One issue that might arise is that Copy will grab the visible cells, but when pasted into col D, the values will go into the top cells, even if those cells are hidden by the filter.

A loop through Range("B:B").SpecialCells(xlCellTypeVisible) is the only solution that I can see.