PDA

View Full Version : Edit Line to change value to ""



YellowLabPro
09-20-2006, 01:47 PM
The following code changes the value in column A to blank if the value in column C is larger than 998.
I would like to change the value in the whole row to blank if the value in column C is larger than 998.
The line is the last line of code, how do I change this?


Thanks,

YLP


Sub PriceLabels()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LRow1 As Long, LRow2 As Long
Dim r As Long

Set ws1 = Sheets("Update")
Set ws2 = Sheets("PriceLabels")
LRow1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Activate
ws2.Range("a2:d" & LRow2).Clear
ws2.Range("A1:D1") = Array("Item#", "Record Description", "Qty", "Price")

ws1.Range("A2:B" & LRow1).Copy ws2.Range("A2")
ws1.Range("G2:G" & LRow1).Copy ws2.Range("C2")
ws1.Range("L2:L" & LRow1).Copy ws2.Range("D2")
With ws2
.Rows("1:1").HorizontalAlignment = xlCenter
.Rows("1:1").Font.Bold = True
.Cells.Columns.AutoFit
.Rows("1:1").HorizontalAlignment = xlCenter
.Rows("1:1").Font.Bold = True
End With

With ws2.Range("A2:D" & LRow2)
.Select
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC3>998"
.FormatConditions(1).Interior.ColorIndex = 1
End With
For r = 2 To LRow2
If Cells(r, "C").Value > 998 Then Cells(r, "A").Value = ""
Next r

End Sub

mvidas
09-20-2006, 02:19 PM
Yelpy,

Change: If Cells(r, "C").Value > 998 Then Cells(r, "A").Value = ""To: If Cells(r, "C").Value > 998 Then Rows(r).ClearContentsMatt

Bob Phillips
09-20-2006, 02:20 PM
If Cells(r, "C").Value > 998 Then Rows(r).Value = ""

Ken Puls
09-20-2006, 02:21 PM
Hi YLP,

Techincally speaking, "" is not quite the same as blank. For all intents and purposes it will work the same, but it isn't actually truly blank.

If you want to clear a cell then try this:
Range("A1").ClearContents

Of for the whole row:
Range("A1").Entirerow.ClearContents

There is also a .Clear method, but it clears everything. (Formatting, validation, etc.)

HTH,

mdmackillop
09-20-2006, 02:21 PM
If Cells(r, "C").Value > 998 Then Rows(r).ClearContents

mdmackillop
09-20-2006, 02:23 PM
An abundance of choice!
:rotflmao:

YellowLabPro
09-20-2006, 02:26 PM
Thanks everyone!!!!!!!!!!!

mvidas
09-20-2006, 02:47 PM
No response for over a half hour but 4 almost identical responses within 2 minutes?
Did we all get home from work at the same time?
:biggrin:

Bob Phillips
09-20-2006, 03:37 PM
Considering we span 8 hour time zones, unlikely.

mvidas
09-20-2006, 03:41 PM
I thought you were in South America, your local time looks to be the same as Malcolm's (GMT?) Visiting abroad or not setup correctly?

Ken Puls
09-20-2006, 03:42 PM
Matt, call Bob British and see what happens. ;)

mvidas
09-20-2006, 03:45 PM
I only act stupid.. :)

Bob Phillips
09-21-2006, 01:00 AM
I thought you were in South America, your local time looks to be the same as Malcolm's (GMT?) Visiting abroad or not setup correctly?
That would still span 8.

Was visiting, haven't changed the flag.