ClearContents behaving differently on two different computers.

[dmprod192]

Alright, I have two identical machines running this macro with the same input file. All reference libraries are consistent, but for some reason one system keeps bugging out on this section of code. It is intended to filter to blank cells containing only "-", select the filtered cells, and then clear the contents.

Both systems will highlight the filtered range, say AB5:AB100. When the ClearContents command is run on the first machine, it only clears the contents of the visible, filtered cells. On the second machine, all unfiltered cells in the range are also cleared. So after this command is run and the filter is turned off, One spreadsheet will still have values in AB10, AB20, AB30, etc. but the other spreadsheet will have this column totally blank.

I am wondering if this might be a system setting or something... Both machines are on Excel 2007. The version numbers vary slightly but I don't think that would matter. I am out of ideas why this is happening.

Thanks in advance for any input!

    ActiveSheet.Range("$A4").AutoFilter Field:=28, Criteria1:="-"
    Range("AB5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveSheet.ShowAllData

 

[ParamRay]

.
.

Change your ClearContents line to:

Selection.SpecialCells(xlCellTypeVisible).ClearContents

This way, only the visible cells will be cleared.