My spreadsheets has rows that are in color w/null values. There are others
that are just null and no color. I need to figure out how to delete only the
rows that have nulls, but not the ones that have color and also null. I have
searched and can't find quite the right response to my question. Thanks for
the help.

Jeremiah

Sub ABC()
Dim cell As Range, r1 As Range
For Each cell In Range("A1:A500")
If cell.Interior.ColorIndex = xlNone Then
If Len(Trim(cell.Text)) = 0 Then
If r1 Is Nothing Then
Set r1 = cell
Else
Set r1 = Union(r1, cell)
End If
End If
End If
Next
If Not r1 Is Nothing Then
r1.EntireRow.Delete
End If

End Sub

worked for me. Assumes the interior color is not produced by conditional
formatting.

--
Regards,
Tom Ogilvy


"jeremiah" wrote:

My spreadsheets has rows that are in color w/null values. There are others
that are just null and no color. I need to figure out how to delete only the
rows that have nulls, but not the ones that have color and also null. I have
searched and can't find quite the right response to my question. Thanks for
the help.

Jeremiah

How about this...

Sub test()
Dim rngUsed As Range
Dim rng As Range
Dim rngColour As Range
Dim rngToDelete As Range
Dim blnColour As Boolean

Set rngUsed = UsedRange.Columns(1).Cells
For Each rng In rngUsed
If Application.WorksheetFunction.CountA(rng.EntireRow ) = 0 Then
For Each rngColour In rng.EntireRow.Cells
If rngColour.Interior.ColorIndex < xlNone Then
blnColour = True
'Exit For
End If
Next rngColour
If blnColour = False Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
blnColour = False
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Select
End Sub
--
HTH...

Jim Thomlinson


"jeremiah" wrote:

My spreadsheets has rows that are in color w/null values. There are others
that are just null and no color. I need to figure out how to delete only the
rows that have nulls, but not the ones that have color and also null. I have
searched and can't find quite the right response to my question. Thanks for
the help.

Jeremiah

Sorry change the select to a delete and remove the comment on the exit for

Sub test()
Dim rngUsed As Range
Dim rng As Range
Dim rngColour As Range
Dim rngToDelete As Range
Dim blnColour As Boolean

Set rngUsed = UsedRange.Columns(1).Cells
For Each rng In rngUsed
If Application.WorksheetFunction.CountA(rng.EntireRow ) = 0 Then
For Each rngColour In rng.EntireRow.Cells
If rngColour.Interior.ColorIndex < xlNone Then
blnColour = True
Exit For
End If
Next rngColour
If blnColour = False Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
blnColour = False
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

How about this...

Sub test()
Dim rngUsed As Range
Dim rng As Range
Dim rngColour As Range
Dim rngToDelete As Range
Dim blnColour As Boolean

Set rngUsed = UsedRange.Columns(1).Cells
For Each rng In rngUsed
If Application.WorksheetFunction.CountA(rng.EntireRow ) = 0 Then
For Each rngColour In rng.EntireRow.Cells
If rngColour.Interior.ColorIndex < xlNone Then
blnColour = True
'Exit For
End If
Next rngColour
If blnColour = False Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
blnColour = False
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Select
End Sub
--
HTH...

Jim Thomlinson


"jeremiah" wrote:

My spreadsheets has rows that are in color w/null values. There are others
that are just null and no color. I need to figure out how to delete only the
rows that have nulls, but not the ones that have color and also null. I have
searched and can't find quite the right response to my question. Thanks for
the help.

Jeremiah

I know I should have posted the code I currently have working thanks to Ron
DeBruin. I neglected to mention that my blanks would be in specific columns
so I cannot look at the entire spreadsheet. How can I ignore those cells
that have color?
Sub Loop_Example2()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet

.Select

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = Lastrow To Firstrow Step -1

If .Cells(Lrow, "c").Value = 0 And _
.Cells(Lrow, "f").Value = 0 And _
.Cells(Lrow, "i").Value = 0 And _
.Cells(Lrow, "l").Value = 0 And _
.Cells(Lrow, "o").Value = 0 Then .Rows(Lrow).Delete

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub



"Jim Thomlinson" wrote:

Sorry change the select to a delete and remove the comment on the exit for

Sub test()
Dim rngUsed As Range
Dim rng As Range
Dim rngColour As Range
Dim rngToDelete As Range
Dim blnColour As Boolean

Set rngUsed = UsedRange.Columns(1).Cells
For Each rng In rngUsed
If Application.WorksheetFunction.CountA(rng.EntireRow ) = 0 Then
For Each rngColour In rng.EntireRow.Cells
If rngColour.Interior.ColorIndex < xlNone Then
blnColour = True
Exit For
End If
Next rngColour
If blnColour = False Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
blnColour = False
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End Sub
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

How about this...

Sub test()
Dim rngUsed As Range
Dim rng As Range
Dim rngColour As Range
Dim rngToDelete As Range
Dim blnColour As Boolean

Set rngUsed = UsedRange.Columns(1).Cells
For Each rng In rngUsed
If Application.WorksheetFunction.CountA(rng.EntireRow ) = 0 Then
For Each rngColour In rng.EntireRow.Cells
If rngColour.Interior.ColorIndex < xlNone Then
blnColour = True
'Exit For
End If
Next rngColour
If blnColour = False Then
If rngToDelete Is Nothing Then
Set rngToDelete = rng
Else
Set rngToDelete = Union(rng, rngToDelete)
End If
End If
blnColour = False
End If
Next rng
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Select
End Sub
--
HTH...

Jim Thomlinson


"jeremiah" wrote:

My spreadsheets has rows that are in color w/null values. There are others
that are just null and no color. I need to figure out how to delete only the
rows that have nulls, but not the ones that have color and also null. I have
searched and can't find quite the right response to my question. Thanks for
the help.

Jeremiah