Can't recreate A Macro
 

At the office yesterday I created a macro << which worked; now that I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c) so --
C5:C50
Run the Macro which would delete the entire row of only cells (in Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then .Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now that I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c) so --
C5:C50
Run the Macro which would delete the entire row of only cells (in Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Ron!! That's Great Thanks a lot..!!

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
..Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells (in Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Just as an aside, I too often create some code and don't save it (for
various reasons). What I tend to find is that, whilst I can usually recall
what I did relatively clearly and so can quickly re-create, this often has a
beneficial effect that upon re-entering I start to think again about the
design/layout/whatever you wish to call it, and often leads to (IMO) a
better piece of code second (or even third) time around.

I guess it is a bit like re-visiting your code, it is always easy to
criticise it and see ways to improve a finished or semi-finished product.

Not advocating this as a development approach of course<vbg.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
..Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells (in Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Ron, as a learning experience I've tried modifying the code and at present
have it
as follows; << It isn't working, but could you comment on what is
apparently wrong
TIA

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long
With Selection ' prior to running macro I've Selected C4:C16 say
StartRow = Selection(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
Cells(Lrow, 3).EntireRow.Delete
End If
Next
End With
End Sub

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
..Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells (in Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Hi JMay

What are you trying to do?
Do you want to make a macro that is also working for the selection
no matter what column you select.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message news:AsYdc.9644$192.6092@lakeread06...
Ron, as a learning experience I've tried modifying the code and at present
have it
as follows; << It isn't working, but could you comment on what is
apparently wrong
TIA

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long
With Selection ' prior to running macro I've Selected C4:C16 say
StartRow = Selection(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
Cells(Lrow, 3).EntireRow.Delete
End If
Next
End With
End Sub

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells (in Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Yes!!

"Ron de Bruin" wrote in message
...
Hi JMay

What are you trying to do?
Do you want to make a macro that is also working for the selection
no matter what column you select.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:AsYdc.9644$192.6092@lakeread06...
Ron, as a learning experience I've tried modifying the code and at
present
have it
as follows; << It isn't working, but could you comment on what is
apparently wrong
TIA

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long
With Selection ' prior to running macro I've Selected C4:C16 say
StartRow = Selection(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
Cells(Lrow, 3).EntireRow.Delete
End If
Next
End With
End Sub

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now
that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I
don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells (in
Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Try this

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long

If Selection.Columns.Count 1 Then Exit Sub
' stop the macro if you select more then one column

With Selection
StartRow = Selection.Cells(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If Cells(Lrow, Selection.Column).Interior.ColorIndex = 6 Then
Cells(Lrow, Selection.Column).EntireRow.Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message news:3OYdc.9646$192.1282@lakeread06...
Yes!!

"Ron de Bruin" wrote in message
...
Hi JMay

What are you trying to do?
Do you want to make a macro that is also working for the selection
no matter what column you select.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:AsYdc.9644$192.6092@lakeread06...
Ron, as a learning experience I've tried modifying the code and at
present
have it
as follows; << It isn't working, but could you comment on what is
apparently wrong
TIA

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long
With Selection ' prior to running macro I've Selected C4:C16 say
StartRow = Selection(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
Cells(Lrow, 3).EntireRow.Delete
End If
Next
End With
End Sub

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in
the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now
that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy I
don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells (in
Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,

Can't recreate A Macro
 

Ron:
I appreciate your assistance in both cases. I've printed out the code of
both versions and plan on getting far away from my computer to compare,
study and hopefully understand.
Thanks again for your help..

"Ron de Bruin" wrote in message
...
Try this

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long

If Selection.Columns.Count 1 Then Exit Sub
' stop the macro if you select more then one column

With Selection
StartRow = Selection.Cells(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If Cells(Lrow, Selection.Column).Interior.ColorIndex = 6 Then
Cells(Lrow, Selection.Column).EntireRow.Delete
End If
Next
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:3OYdc.9646$192.1282@lakeread06...
Yes!!

"Ron de Bruin" wrote in message
...
Hi JMay

What are you trying to do?
Do you want to make a macro that is also working for the selection
no matter what column you select.



--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:AsYdc.9644$192.6092@lakeread06...
Ron, as a learning experience I've tried modifying the code and at
present
have it
as follows; << It isn't working, but could you comment on what is
apparently wrong
TIA

Sub DeleteYellowRow()
Dim Lrow As Long
Dim StartRow As Long
Dim EndRow As Long
With Selection ' prior to running macro I've Selected C4:C16
say
StartRow = Selection(1).Row
EndRow = StartRow + Selection.Rows.Count - 1
For Lrow = EndRow To StartRow Step -1
If .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
Cells(Lrow, 3).EntireRow.Delete
End If
Next
End With
End Sub

"Ron de Bruin" wrote in message
...
Try this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 5
EndRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a
error in
the
cell

ElseIf .Cells(Lrow, "C").Interior.ColorIndex = 6 Then
.Rows(Lrow).Delete
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"JMay" wrote in message
news:rbSdc.9193$192.4538@lakeread06...
At the office yesterday I created a macro << which worked; now
that
I'm
home
I can't seem to re-create it,,, why I didn't mail my self a copy
I
don't
know...
Anyway

It worked as follows:

I have a data table say A5:F50,
I could select (highlight) a single column range, (always Col c)
o --
C5:C50
Run the Macro which would delete the entire row of only cells
(in
Col C)
with interior.colorindex = 6.

Can someone help me re-create?
TIA,