VBA Macro for Range().End(xlUp).Row works for middle of column but oddly, not top/bottom

[sample spreadsheet attached]

My spreadsheet contains multiple rows of data, but the number of rows changes each day. Further, in Column A, there will always be exactly 4 contiguous rows of data, and I need the macro to make bold only the "cell" (Column A) of the first row in each series of data.

Unfortunately, each series in Column A does not follow a neat predictable order, because the spacing of rows is ultimately determined by data contained in Columns B through P (although not really relevant to get this macro to work). The logic I tried to use was to identify each series of data by first finding a cell that contained a value > 0 and then searching one row above and below that cell to check which place in that particular series of data it occupied (i.e. first, second, third, or fourth position), and finally using that positioning to change the fontstyle to bold for the cell occupying the first position of the data series.

So, if you refer to the attached spreadsheet, I would like cells: A2 ("Company A"), A8 ("Company B"), and A17 ("Company C") to turn bold. When I ran my code on the original spreadsheet with 120 rows (i.e. they can get as large as 5,000), the equivelent of only cell A8 ("Company B") turned bold while cells A2 ("Company A") and A17 ("Company C") remained unchanged.

Any help would be much appreciated.

Please Login or Register  to view this content.

justinbelkin,

Welcome to the Excel Forum.

Thanks for the workbook.

It would appear that you have titles in row 1.

If that is not true, and your first group starts in A1, then:


Change this line of code:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the FontStyleBoldV2 macro.

Hi justin

Welcome to the Forum!

If I understand your requirement try this

Please Login or Register  to view this content.

justinbelkin,

You are very welcome. Glad we could help.

Thanks for the feedback.

And, come back anytime.