CODEWITHSUNDEEP
excelvba
Revathi kannan
Revathi kannan

Reputation: 33

Excel vba : Help for Grid lines

I have details from Column A to column M in Sheet 1.

I need to implement Grid lines for the same from Column A to M.

The complication part is, there are 3 different tables in sheet 1 wherein i need to implement Grid lines separately for all those 3 tables based on Used range.

I have macro for the same but it is just working by implementing grid lines altogether for 3 tables. Refer the screenshot for output.

Screenshot

Upvotes: 1

Views: 2376

Answers (2)

DisplayName
DisplayName

Reputation: 13386

standing your data layout example, you could also use SpecialCells() method of Range object

Dim area As Range

For Each area In ActiveSheet.UsedRange.SpecialCells(XlCellType.xlCellTypeConstants).Areas
    With area.Borders
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With
Next

Upvotes: 1

user4039065
user4039065

Reputation:

Use the range.CurrentRegion property to isolate each data block.

dim i as long

with worksheets("sheet1")

    if isempty(.cells(1, "A")) then
        i = .cells(1, "A").end(xldown).row
    else
        i = 1
    end if

    do while i < .rows.count

        with .cells(i, "A").currentregion
            'do the border formatting here
            'example:
            With .Borders
                .LineStyle = xlContinuous
                .Color = vbRed
                .Weight = xlThin
            End With

            debug.print .address(0, 0)
        end with

        i = .cells(i, "A").end(xldown).end(xldown).row

    loop

end with

Upvotes: 0

Related Questions