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
|
Similar Threads
