I'd like to fill the empty rows in a table gray but from A:G.
Currently I have the following:
' Colors the empty rows gray
This fills the entire row gray rather than stopping at the G column.
Additionally, I'd like to number empty rows beginning in the A column up until the end of the data. The issue I have is that the table will be dynamic (Could go from Row 1 to Row 40 one week and then from Row 1 to Row 70 the next week). I'm having a hard time trying to figure out how to number the data within the table but stopping before numbering an entire sheet.
Overall Code here:
Currently I have the following:
' Colors the empty rows gray
VBA Code:
Dim rng As Range
On Error Resume Next
Set rng = Range("A1:G").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Interior.ColorIndex = 15
End If
This fills the entire row gray rather than stopping at the G column.
Additionally, I'd like to number empty rows beginning in the A column up until the end of the data. The issue I have is that the table will be dynamic (Could go from Row 1 to Row 40 one week and then from Row 1 to Row 70 the next week). I'm having a hard time trying to figure out how to number the data within the table but stopping before numbering an entire sheet.
Overall Code here:
VBA Code:
Sub Button1_Click()
' Deletes Columns Unnecessary Data
Sheets("Sheet1").Range("B:B, D:D, I:M").EntireColumn.Delete
' Sorts Data in Alpha Numerical Order using Column "D"
Range("D1").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlYes
' Adds a Spaced Row After each Column D Line is found to be Different
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 2 Step -1
If Left(Cells(lRow, "D"), 7) <> Left(Cells(lRow - 1, "D"), 7) Then
Rows(lRow).EntireRow.Insert
' Adds a gray filled bar in empty rows
Rows(lRow).Interior.ColorIndex = 15
End If
Next lRow
' Colors the empty rows gray
Dim rng As Range
On Error Resume Next
Set rng = Range("A1:G").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Interior.ColorIndex = 15
End If
' Auto Fits Rows and Columns based off of the data
Sheets(1).UsedRange.Columns.AutoFit
Sheets(1).UsedRange.Rows.AutoFit
' Creates the Notes Section in G1
Range("G1") = "Notes"
' Creates a border around the data to make it look pretty :)
Range("A1:G" & Range("A" & Rows.Count).End(xlUp).Row).Borders.LineStyle = xlContinuous
Application.ScreenUpdating = True
End Sub