All border using VB macro

[ashokbioinfo]

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

 

[Wookiee]

Does this work for your purposes?

[pre]

<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]

 

[ashokbioinfo]

thanks a lot Wookiee

 

[ashokbioinfo]

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

 

[Wookiee]

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]

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]

 

[ashokbioinfo]

thanks Wookiee


Its working now