none
regarding creat code to make bold border for unempty rows in excel sheet RRS feed

  • Question

  • dears ,

    good greeting

    i'm searching for one code to make borders around each unempty rows in excel sheet

    how can i made it within vba code,

    regards................. 

    Wednesday, April 11, 2018 1:01 PM

All replies

  • Hello TAREK SHARAF,

    I think the key point is how to confirm the row is unempty row.

    I would suggest you compare the row's whole cells count the blank cells count. If they are same value, the row is an empty row.

    Once you call tell this is an empty/unempty row, then you could set its border.

    Here is the simply code.

    Sub Test()
    Dim WS As Worksheet
    Set WS = ActiveSheet
    Dim rowRng As Range
    For Each rowRng In WS.UsedRange.Rows
    If rowRng.SpecialCells(xlCellTypeBlanks).Cells.Count <> rowRng.Cells.Count Then
    With rowRng
    .Borders.Weight = xlThick
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    End If
    Next rowRng
    End Sub
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 12, 2018 2:40 AM
  • dear mr / terry

    good greeting

    it give me the following error,

    Thursday, April 12, 2018 4:44 AM
  • Hello TAREK SHARAF,

    Sorry for my careless of testing. If there is no blank cells in the range, rowRng.SpecialCells(xlCellTypeBlanks).Cells.Count will give the error.

    I would suggest you use CountBlank function to count blank cells of the range.

    Here it is.

    For Each rowRng In WS.UsedRange.Rows
    BlanckCellsCount = WorksheetFunction.CountBlank(rowRng)
    If BlanckCellsCount <> rowRng.Cells.Count Then
    With rowRng
    .Borders.Weight = xlThick
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With
    End If
    Next rowRng

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 12, 2018 5:16 AM
  • dear Mr / Terry

    thank's too much it;s working well,

    but if i want to make borders for each cell not just each row how can i made it

    regards..............

    Thursday, April 12, 2018 5:30 AM
  • Hello ,

    Which result would you want ?

    Or

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 12, 2018 5:37 AM
  • dear Mr / terry

    i want all the unempty cells to be bold borders

    shape no 2

    regards...

    Thursday, April 12, 2018 5:47 AM
  • Hello Tarek Sharaf,

    Please delete below two line from the code.

    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 12, 2018 5:50 AM
  • dear Mr /Terry

    thanks too much it work well

    thank's again for your effort with me my friend 

    accept my appreciation and respect,

    Thursday, April 12, 2018 6:02 AM
  • Hi TAREK,

    I am glad your issue has been resolved, and I would suggest you mark the helpful reply as answer which is the way to close a thread here.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 24, 2018 6:13 AM