Jarlisle
04-28-2009, 10:59 AM
The following code is used when I have some product numbers in column A and the corresponding Qty. in column B, but if there happens to be a product number in column A and nothing in B it will delete the column A contents. It works just fine unless there is only 1 number in column B and then the "Selection.End(xlDown).Select" statement moves too far down the sheet. I only want it to delete to row 37.
Application.ScreenUpdating = False
Range("A16:B37").Select
Range("B16").Activate
ActiveWorkbook.Worksheets("Wizard").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Wizard").Sort.SortFields.Add Key:=Range("B16"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Wizard").Sort
.SetRange Range("A16:B37")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B16").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(ActiveCell, "A37").ClearContents
Range("A16").Select
Application.ScreenUpdating = False
Range("A16:B37").Select
Range("B16").Activate
ActiveWorkbook.Worksheets("Wizard").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Wizard").Sort.SortFields.Add Key:=Range("B16"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Wizard").Sort
.SetRange Range("A16:B37")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B16").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, -1).Range("A1").Select
Range(ActiveCell, "A37").ClearContents
Range("A16").Select