Conditional Bordering Code!

[Jayce]

In my sheet, I have non-empty and empty cells in Column A. I want to border around the cells in Column A prior to the next non-empty cells (please check End-Result Sheet of the attached workbook)
I am attaching a workbook with two sheets: Raw Sheet that needs coding and End-Result Sheet to see the results I am looking for.

 

[herofox]

HI

 

[Jayce]

Hi Herofox,

This is not what I am looking for. You are just bordering non-empty cells. I want to create border around all the empty cells following a non-empty cell, for instance, A3:A6 because A4:A6 cells are empty
A8:A11 as A9:A11 are empty

If a non-empty cell is followed by non-empty cells, then I want to create a border around each non-empty cells cell. For eg, a border around A7 only

 

[Fluff13]

How about

Sub Jayce()
   With Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlConstants).Borders.Weight = xlThin
      With .SpecialCells(xlBlanks)
         .Borders(xlEdgeTop).LineStyle = xlNone
         .Borders(xlEdgeBottom).Weight = xlThin
      End With
   End With
End Sub

 

[Peter Bartholomew]

I tested for text and inserted the border above.
Numbers do not affect the CF.

 

[Jayce]

Great Job, Fluff13

It's working pretty well.

Could you please add the comments in code, so that it becomes easy to understand the working of script

 

[salim hasan]

try this macro

Option Explicit
Sub BORDER_IT()
 With Range("a1").CurrentRegion
  .Borders.LineStyle = 0
    With .Columns(1).SpecialCells(2, 23)
     .Borders.LineStyle = 1
     .Offset(, 1).Borders.LineStyle = 1
    End With
 End With
End Sub

 

[navic]

I want to create border around all the empty cells following a non-empty cell, for instance, A3:A6 because A4:A6 cells are empty
A8:A11 as A9:A11 are empty

I don't think VBA is needed to solve this problem.

I will add another CF condition (to the examples set by @herofox and @Peter Bartholomew) and the result is the same as using the VBA set by @Fluff13 btw. which is excellent.

This formula below, returns the row number of the last filled cell in the 'B' column.

Named formula: LastRow
References: =SUMPRODUCT(MAX(($B$1:$B$130<>"")*ROW($B$1:$B$130)))

Formula in the Conditional Formatting is below

=ROW()=LastRow

Another Conditional Formatting formula
=ISTEXT(A3) by the PB
or
=$A3<>"" by the Herofox

 

[Fluff13]

Great Job, Fluff13

It's working pretty well.

Could you please add the comments in code, so that it becomes easy to understand the working of script

You're welcome & thanks for the feedback.

Sub Jayce()
   With Range("A2", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
      .SpecialCells(xlConstants).Borders.Weight = xlThin             'applies borders to all cells in col A that contain a value (other than formulae)
      With .SpecialCells(xlBlanks)                                   '"selects" all cells in col A that are blank
         .Borders(xlEdgeTop).LineStyle = xlNone                      'removes the top borders
         .Borders(xlEdgeBottom).Weight = xlThin                      'applies a bottom border
      End With
   End With
End Sub

 

[Jayce]

Great Help....

 

[Fluff13]

My pleasure