• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

All border using VB macro

ashokbioinfo

New Member
Dear all,

In my datasheet, I have four variables. I need to make all border from cell(2,2) to last row along with last column until I encounter blank row and column.


Pat_id Serio BODY_SYS PREF_TERM

Pat_001 Ser Skin and subcutaneous tissue disorders Erythema

Pat_002 Ser Immune system disorders Hypersensitivity

Pat_003 Nonser Nervous system disorders Paraesthesia

Pat_004 Ser Skin and subcutaneous tissue disorders Erythema

Pat_005 Nonser Skin and subcutaneous tissue disorders Erythema

Pat_006 Ser Skin and subcutaneous tissue disorders Erythema


What macro I should write

Advance thanks
 
Does this work for your purposes?

[pre]
Code:
<code>
Sub BorderNonBlanks()

'Declare Variables
Dim lngLastRow As Long, lngLastCol As Long

'Determine Last Row With Data
lngLastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
lngLastCol = ActiveSheet.Cells(2, 2).End(xlToRight).Column

'Set Borders On Range of Non-Blank Cells
Range(Cells(2, 2), Cells(lngLastRow, lngLastCol)).Borders.LineStyle = xlContinuous

End Sub
</code>
[/pre]
 
One problem here is that the macro is restricted to row only, where as border are selected for all column. I dont want border whereever I come across blank cell in rows or columns
 
I've revised the macro so that it only places a border on non-blank cells. I hope this is a solution that will work for you.

[pre]
Code:
Sub CellBorderNonBlanks()

'Declare Variables
Dim lngLastRow As Long, lngLastCol As Long, rngCell As Range

'Determine Last Row and Column With Data
lngLastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
lngLastCol = ActiveSheet.Cells(2, 2).End(xlToRight).Column

'Set Borders On Range of Non-Blank Cells
For Each rngCell In Range(Cells(2, 2), Cells(lngLastRow, lngLastCol))
If Not IsEmpty(rngCell) Then rngCell.Borders.LineStyle = xlContinuous
Next rngCell

End Sub
[/pre]
 
Back
Top