Auto format blank rows using code
 

Hi
I need to have a spreadsheet searched and where there are complete blank
rows I need to colour that row. I can't do this using conditional formatting
as there will be data constantly copied into the spreadsheet overwriting any
conditional formatting input, therefore it needs to be done using VB code. I
already have some code in the workbook (see below) and would like to add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
..HorizontalAlignment = xlGeneral
..VerticalAlignment = xlBottom
..WrapText = False
..Orientation = 0
..AddIndent = False
..IndentLevel = 0
..ShrinkToFit = False
..ReadingOrder = xlContext
..MergeCells = False

..EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes

Auto format blank rows using code
 

Try this small macro:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).Interior.ColorIndex = 3
End If
Next
End Sub

--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

Hi
I need to have a spreadsheet searched and where there are complete blank
rows I need to colour that row. I can't do this using conditional formatting
as there will be data constantly copied into the spreadsheet overwriting any
conditional formatting input, therefore it needs to be done using VB code. I
already have some code in the workbook (see below) and would like to add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes

Auto format blank rows using code
 

WOW, that was shockingly bright - however, worked a treat. Are you able to
help me with an amendment - instead of formatting the blank row, how would I
find the blank row and then format the row above it instead? Thanks in
advance.
--
Marie Bayes


"Gary''s Student" wrote:

Try this small macro:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).Interior.ColorIndex = 3
End If
Next
End Sub

--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

Hi
I need to have a spreadsheet searched and where there are complete blank
rows I need to colour that row. I can't do this using conditional formatting
as there will be data constantly copied into the spreadsheet overwriting any
conditional formatting input, therefore it needs to be done using VB code. I
already have some code in the workbook (see below) and would like to add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes

Auto format blank rows using code
 

A one line change:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
i = 20
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j - 1).Interior.ColorIndex = 3
End If
Next
End Sub
--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

WOW, that was shockingly bright - however, worked a treat. Are you able to
help me with an amendment - instead of formatting the blank row, how would I
find the blank row and then format the row above it instead? Thanks in
advance.
--
Marie Bayes


"Gary''s Student" wrote:

Try this small macro:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).Interior.ColorIndex = 3
End If
Next
End Sub

--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

Hi
I need to have a spreadsheet searched and where there are complete blank
rows I need to colour that row. I can't do this using conditional formatting
as there will be data constantly copied into the spreadsheet overwriting any
conditional formatting input, therefore it needs to be done using VB code. I
already have some code in the workbook (see below) and would like to add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes

Auto format blank rows using code
 

Ignore my second post. Use:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j - 1).Interior.ColorIndex = 3
End If
Next
End Sub
--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

WOW, that was shockingly bright - however, worked a treat. Are you able to
help me with an amendment - instead of formatting the blank row, how would I
find the blank row and then format the row above it instead? Thanks in
advance.
--
Marie Bayes


"Gary''s Student" wrote:

Try this small macro:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).Interior.ColorIndex = 3
End If
Next
End Sub

--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

Hi
I need to have a spreadsheet searched and where there are complete blank
rows I need to colour that row. I can't do this using conditional formatting
as there will be data constantly copied into the spreadsheet overwriting any
conditional formatting input, therefore it needs to be done using VB code. I
already have some code in the workbook (see below) and would like to add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes

Auto format blank rows using code
 

Marie,

Change the line Rows(j)..... to this:

Rows(j - 1).Interior.ColorIndex = 3

Regards,
Per

"Marie Bayes" skrev i meddelelsen
...
WOW, that was shockingly bright - however, worked a treat. Are you able
to
help me with an amendment - instead of formatting the blank row, how would
I
find the blank row and then format the row above it instead? Thanks in
advance.
--
Marie Bayes


"Gary''s Student" wrote:

Try this small macro:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).Interior.ColorIndex = 3
End If
Next
End Sub

--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

Hi
I need to have a spreadsheet searched and where there are complete
blank
rows I need to colour that row. I can't do this using conditional
formatting
as there will be data constantly copied into the spreadsheet
overwriting any
conditional formatting input, therefore it needs to be done using VB
code. I
already have some code in the workbook (see below) and would like to
add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes

Auto format blank rows using code
 

Once again you haven't let me down! Great result, thanks so much.
--
Marie Bayes


"Gary''s Student" wrote:

Ignore my second post. Use:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j - 1).Interior.ColorIndex = 3
End If
Next
End Sub
--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

WOW, that was shockingly bright - however, worked a treat. Are you able to
help me with an amendment - instead of formatting the blank row, how would I
find the blank row and then format the row above it instead? Thanks in
advance.
--
Marie Bayes


"Gary''s Student" wrote:

Try this small macro:

Sub ColorEmpties()
Dim i As Long, j As Long
i = Rows.Count
For j = 1 To i
If Application.WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).Interior.ColorIndex = 3
End If
Next
End Sub

--
Gary''s Student - gsnu200850


"Marie Bayes" wrote:

Hi
I need to have a spreadsheet searched and where there are complete blank
rows I need to colour that row. I can't do this using conditional formatting
as there will be data constantly copied into the spreadsheet overwriting any
conditional formatting input, therefore it needs to be done using VB code. I
already have some code in the workbook (see below) and would like to add to
it with the above. I'd appreciate any help. Thanks.
Private Sub workbook_open()
Sheets("Sheet2").Activate

Set f = Columns("A").Find(what:="Date")
Set TestRange = Range(f.Address).Offset(-2, 0).Resize(2, 1)

If WorksheetFunction.CountA(TestRange.Value) = 0 Then
TestRange.EntireRow.Delete
End If

With Cells
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

.EntireColumn.AutoFit
End With

On Error GoTo errhandler
ActiveSheet.DrawingObjects.Delete

errhandler:
Exit Sub
End Sub
--
Marie Bayes