Deleting unwanted data by macro or...

  • Thread starter Thread starter Leibtek
  • Start date Start date
L

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