delete row syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Sub deleteColoredRows()

Const LtGray = 15
Const DkGray = 16
Range("A1").EntireRow.Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).EntireRow.Select


Selection.Interior.ColorIndex = LtGray Or DkGray
EntireRow.Delete

Loop

End Sub

The loop works, the only thing wrong is the delete line.
If the row is either shade of gray I want it to delete.
what is the correct syntax for the delete line?
many thanks,
 
Sub deleteColoredRows()
Dim lngRow As Long
Dim lngN As Long
Const LtGray As Long = 15
Const DkGray As Long = 16
lngRow = Cells(Rows.Count, 1).End(xlUp).Row

For lngN = lngRow To 2 Step -1
If Cells(lngN, 1).Interior.ColorIndex = LtGray Or _
Cells(lngN, 1).Interior.ColorIndex = DkGray Then
Cells(lngN, 1).EntireRow.Delete
End If
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Janis" <[email protected]>
wrote in message
Sub deleteColoredRows()

Const LtGray = 15
Const DkGray = 16
Range("A1").EntireRow.Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).EntireRow.Select

Selection.Interior.ColorIndex = LtGray Or DkGray
EntireRow.Delete

Loop
End Sub

The loop works, the only thing wrong is the delete line.
If the row is either shade of gray I want it to delete.
what is the correct syntax for the delete line?
many thanks,
 
Give this a try... (untested but it should be close)

Sub deleteColoredRows()
Dim rng As Range
Dim rngToSearch As range
Dim rngToDelete as Range
Const LtGray = 15
Const DkGray = 16

With Activesheet
Set rngToSearch = .Range(.Range("A1"), .Cells(rows.count, "A").end(xlUp))
end with
for each rng in rngToSearch
if rng.Interior.ColorIndex = LtGray Or _
rng.Interior.ColorIndex = DkGray then
if rngtodelete is nothing then
set rngToDelete = rng
else
set rngtodelete = union(rngToDelete, rng)
end if
end if
next rng
if not rngtodelete is nothing then rngtoDelete.entirerow.delete

End Sub
 
I have a question, what does Ing mean?
Thanks again for the macro, trial and error was getting to me.
 
I tried it and it worked!
Jim Thomlinson said:
Give this a try... (untested but it should be close)

Sub deleteColoredRows()
Dim rng As Range
Dim rngToSearch As range
Dim rngToDelete as Range
Const LtGray = 15
Const DkGray = 16

With Activesheet
Set rngToSearch = .Range(.Range("A1"), .Cells(rows.count, "A").end(xlUp))
end with
for each rng in rngToSearch
if rng.Interior.ColorIndex = LtGray Or _
rng.Interior.ColorIndex = DkGray then
if rngtodelete is nothing then
set rngToDelete = rng
else
set rngtodelete = union(rngToDelete, rng)
end if
end if
next rng
if not rngtodelete is nothing then rngtoDelete.entirerow.delete

End Sub
Click to expand...
 
Janis,
I try to identify all variables with a prefix that identifies the data type of the variable...
lng for Long
str for String
rng for Range
obj for Object
dbl for Double
sng for Single
bln for Boolean
and so on.

It is a habit I got into and it is a practice that many recommend.
Some code can be thousands of lines and it is very helpful, when trying
to decipher the code. Is "Row" a number, a range object or something else.
lngRow lets you know what it is without having to refer to the declarations
section or being forced to make a cheat sheet.

It also allows you to use "key" words in Excel without causing a conflict.
You would not want to use "Row" as a variable as Excel has its own definition.

Believe me, if you are trying to figure out someone else's code or review
your own code, that is more than a couple weeks old, you will appreciate it.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"Janis" <[email protected]>
wrote in message
I have a question, what does Ing mean?
Thanks again for the macro, trial and error was getting to me.
 
Back
Top