Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
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" 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
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 -- HTH... Jim Thomlinson "Janis" wrote: 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
I have a question, what does Ing mean?
Thanks again for the macro, trial and error was getting to me. "Jim Cone" wrote: 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" 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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
I tried it and it worked! "Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "Janis" wrote: 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, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
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" wrote in message I have a question, what does Ing mean? Thanks again for the macro, trial and error was getting to me. "Jim Cone" wrote: 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" 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, |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row syntax
thanks, for getting me started right. I'm in SF too.
"Jim Cone" wrote: 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" wrote in message I have a question, what does Ing mean? Thanks again for the macro, trial and error was getting to me. "Jim Cone" wrote: 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" 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete syntax problem | Excel Worksheet Functions | |||
Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
Delete Row syntax conundrum | Excel Discussion (Misc queries) | |||
Delete Row syntax conundrum | New Users to Excel | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |