Macro clearing cells

[SJC]

I have a workbook with several worksheets. One column of each worksheet has linked cells to carry the data over to the next spreadsheet. In addition to the data that is carried over, the user can then add to the column of data on each sheet. I then have a sort macro that would clear the remainder of cells not used and alphabetize the ones that were used. This macro worked fine for a long time. However, now suddently this macro is not clearing out the cells that are not used. They continue to have the linked cell, which then returns a value of '0' which disrupts the sort function. I have included the code below--any ideas on how to fix? The file was created in Excel 2007, which is the program currently being used as well. However, it is saved as an .xls file so that users of multiple excel versions can use. I have attached a part of the file to show the issue. Thanks for any assistance.

Code: Select all

Sub Button53_Click()
Dim i


    ActiveSheet.Unprotect Password:="qirocks"
    
    For Each i In [B8:B139]
    If i.Value = 0 Then
        i.Value = ""
    End If

Next
Range("B8:AM137").Select
    Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveSheet.Protect Password:="qirocks", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

[HansV]

Welcome to Eileen's Lounge!

Your actual code had

If i.Value = "0" Then

which is slighly but significantly different.

Try this version of the code:

Code: Select all

Sub Button53_Click()
    Dim i As Range
    ActiveSheet.Unprotect Password:="qirocks"
     For Each i In [B8:B137]
         If i.Value = 0 Then
            i.ClearContents
        End If
    Next
    Range("B8:BB137").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlTrue
    ActiveSheet.Protect Password:="qirocks", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

[SJC]

Thanks very much for this suggestion and assistance. It works great.