I wrote a macro to filter a list of records and copy/paste the matching records to a new worksheet. Each matching record had at least one more that shared the same file ID (i.e. multiple records per file ID), and in order to separate them and make the list easier to read, I copied the file ID once for each set of records per ID. For example, my result set looked like:
File ID Record
1 Bob
John
2 Henry
Jan
Mary
This works fine except that I want to place a border along the top of the cell containing the file ID, so that each section is surrounded by a border. The record information is already bordered in the original list, so when I copy it from that list, the border comes with it.
Now here's the weird part. The original list was copied directly from a pivot table that was grouped by file ID numbers. When I copied the original data from that pivot table into a blank worksheet, it copied all the formatting, borders, etc., including the "blank" borders around the empty cells below each file ID, such that:
File ID Record
1 Bob
<empty> John
2 Henry
<empty> Jan
<empty> Mary
When my macro filtered the resulting list (based on other criteria) and copied specific records to a third worksheet (I was copying the entire row), it copied this "blank" border as well, and whenever I tried to set the border properties (LineStyle, Weight, ColorIndex), I got an error that said "unable to set LineStyle property of the Borders class." I tried a few suggestions that I found online, but nothing seemed to work. I finally found that as long as there was already *some* kind of border around the cell (even if it was just the visible gridlines), the macro processed normally and created the correct borders.
So after all that, my question is this: what makes this "blank" border and how can I code my macro to account for it? I was checking some of the border properties and found that the LineStyle of the blank border had an integer value of -4142. Same thing with the ColorIndex.
Any ideas?
File ID Record
1 Bob
John
2 Henry
Jan
Mary
This works fine except that I want to place a border along the top of the cell containing the file ID, so that each section is surrounded by a border. The record information is already bordered in the original list, so when I copy it from that list, the border comes with it.
Now here's the weird part. The original list was copied directly from a pivot table that was grouped by file ID numbers. When I copied the original data from that pivot table into a blank worksheet, it copied all the formatting, borders, etc., including the "blank" borders around the empty cells below each file ID, such that:
File ID Record
1 Bob
<empty> John
2 Henry
<empty> Jan
<empty> Mary
When my macro filtered the resulting list (based on other criteria) and copied specific records to a third worksheet (I was copying the entire row), it copied this "blank" border as well, and whenever I tried to set the border properties (LineStyle, Weight, ColorIndex), I got an error that said "unable to set LineStyle property of the Borders class." I tried a few suggestions that I found online, but nothing seemed to work. I finally found that as long as there was already *some* kind of border around the cell (even if it was just the visible gridlines), the macro processed normally and created the correct borders.
So after all that, my question is this: what makes this "blank" border and how can I code my macro to account for it? I was checking some of the border properties and found that the LineStyle of the blank border had an integer value of -4142. Same thing with the ColorIndex.
Any ideas?
