Can't recreate A Macro

J

JMay

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,
 
R

Ron de Bruin

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
 
J

JMay

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

Ron de Bruin said:
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
Click to expand...
 
B

Bob Phillips

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)
 
J

JMay

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
 
R

Ron de Bruin

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.
 
J

JMay

Yes!!

Ron de Bruin said:
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.
Click to expand...
 
R

Ron de Bruin

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
 
J

JMay

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..
 
Top