Deleting unwanted data by macro or...

[Leibtek]

I posted this question in the "Excel Programming" thread, but this seems
to be too simple for that thread.

I have a list that looks like this:

A...........B...........C...........D
Item......Size1......Size2.....Size3 (Header)
Item1.......$2........$3.........$4
Item2.......$1........$2.........$3
Item3.......$4........$5.........$6

"Item2" only comes in the first two sizes (Size1, and Size2), so I want
to add a column where I would specify the max size for "Item2", and a
macro would delete the Size3 price for Item2. (I would do this to all
items in my list)

Something like this:

A...........B...........C...........D...........E
Item......Size1......Size2.....Size3....MaxSize (Header)
Item1.......$2........$3.........$4...........3
Item2.......$1........$2.......................2
Item3.......$4........$5.........$6...........3

Thank You,
Leibtek

 

[mrice]

Try the following user defined function which you need to place on the
tab corresponding to your sheet of data (e.g. sheet1) in the VBA
editor.

Private Sub Worksheet_Change(ByVal Target As Range)
MaxColumn = 5
If Target.Column = MaxColumn And Target.Columns.Count = 1 And
Target.Rows.Count = 1 And IsNumeric(Target) Then
For N = MaxColumn - 1 To Int(Target.Value) + 2 Step -1
Cells(Target.Row, N).ClearContents
Next N
End If
End Sub

 

[Leibtek]

Thanks, - Worked Great.