Limiting table formatting to cells with data/content

VRM

New Member
Joined
Jun 27, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,
First, I must say that this community is AWESOME.
You willingness to help and so quickly is greatly appreciated.

The following macro will be used on tables with different numbers of rows.
It formats all of the rows in the columns.
Is there a way of having only the rows with data formatted?
VBA Code:
Range("D:E,G:G").Select
    Range("G1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("A:J").Select
    Selection.Columns.AutoFit
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "KHub Project " & Chr(10) & "Completeness"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Has Proposal " & Chr(10) & "Document"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Has Project " & Chr(10) & "Document"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Has a Project " & Chr(10) & "Description"
    Columns("C:G").Select
    Selection.ColumnWidth = 16
    Columns("H:H").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 70
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Abt " & Chr(10) & "Organization"
    Range("K1").Select
    Columns("J:J").ColumnWidth = 13.57
    Columns("A:J").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("H:H").Select
    Range("A1").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A:J"), , xlYes).Name = _
        "Table31"
    ActiveSheet.ListObjects("Table31").TableStyle = "TableStyleLight21"
    Columns("C:C").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 13434879
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Your assistance is appreciated. Thanks.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jamiemarie

Board Regular
Joined
Jun 24, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Bumping as I'm encountering the same issue and can't resolve.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
The following macro will be used on tables with different numbers of rows.
It formats all of the rows in the columns.
Is there a way of having only the rows with data formatted?
Which formatting in your code are you referring to? Please specify based on the cleaned up version of the code below...
VBA Code:
Sub xxxx()
    Range("D:E,G:G").Delete Shift:=xlToLeft
    Columns("A:J").Columns.AutoFit
    Range("C1").Value = "KHub Project " & Chr(10) & "Completeness"
    Range("D1").Value = "Has Proposal " & Chr(10) & "Document"
    Range("E1").Value = "Has Project " & Chr(10) & "Document"
    Range("F1").Value = "Has a Project " & Chr(10) & "Description"
    
    Columns("C:G").ColumnWidth = 16
    
    With Columns("H:H")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .ColumnWidth = 70
    End With
    
    Range("J1").Value = "Abt " & Chr(10) & "Organization"
    Columns("J:J").ColumnWidth = 13.57
  
    With Columns("A:J")
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
 
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("A:J"), , xlYes).Name = _
                                                                        "Table31"
    ActiveSheet.ListObjects("Table31").TableStyle = "TableStyleLight21"
 
    With Columns("C:C").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 13434879
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,955
Members
410,714
Latest member
T_Bos
Top