Automatically adjust gridlines to only cover rows with content

[vikrampnz]

Hello

I am using Excel 2007. I have a table from which I have created two dynamic tables based on a value in a specific column. I am doing this using pivot table.

I am wondering if there is a way I can automatically apply the gridlines only to rows that have contents, as the two tables are dynamic and based on the value in a specific column the rows in one table increases and same number of rows in the second table decreases.

Both these table are placed in the same worksheet below one another. When I initially place them, I have left 3 blank rows, but when I change options in columns and the number of rows changes in the two tables, the gap (number of rows) between the two tables increases or decreases. Also, the grip lines dont change with this.

Can someone please help?

TIA

 

[Beneindias]

You need to create conditional formating.
Add gridlines, if cell is not empty ( Cell <> "")

 

[Flaiban]

I have a code that I use that can help you . Just adapt to your spreadsheet :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, Range("A2:M" & Lastrow)) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    With Target.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlHairline
    End With
    With Target.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlHairline
    End With
    With Target.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlHairline
    End With
    With Target.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlHairline
    End With
    
Else

    With Target.Borders(xlEdgeBottom)
        .LineStyle = xlNone

    End With
    With Target.Borders(xlEdgeLeft)
        .LineStyle = xlNone

    End With
    With Target.Borders(xlEdgeRight)
        .LineStyle = xlNone
    End With
    With Target.Borders(xlInsideVertical)
        .LineStyle = xlNone
    End With
    With Target.Borders(xlInsideHorizontal)
        .LineStyle = xlNone
    End With

End If

End Sub

 

[Georgiboy]

If it is a range on a sheet rather than an actual table object then maybe something like:

Sub test()
    With Range("A1").CurrentRegion
        .Borders.LineStyle = xlNone
        .SpecialCells(xlCellTypeConstants).Borders.LineStyle = xlContinuous
        .SpecialCells(xlCellTypeFormulas).Borders.LineStyle = xlContinuous
    End With
End Sub

 

[vikrampnz]

Thank you so much Beneindias, Flaiban & Geogiboy for prompt responses.

I used the conditional formatting option and it worked brilliantly.

The problem I am now facing is the gap between the two tables. Is there a way, I can get the bottom table to move up or down automatically, depending upon the last row of the top table? I need to have only 2 blank rows between the two tables.

(they are both exactly identically structured tables, just split in two. Both the table get the source data from the same master table. I am selecting two separate filter selections two separate two lists based on a criteria in a column.

Thanks again