0

Is it possible to clear a large number of cells based on a value, i.e. if >1? I am using Excel for Mac 2011.

I would like to convert thousands of values >1 to empty cells in a large dataset (600 rows x 450K). The values are supposed to range from 0 to 1, but there are errors scattered throughout where the entry is >1 (1000-10000) and precludes averaging rows accurately.

BTW: I tried the previously posted macro for "clear cells based on color" after highlighting all cells with values >1, but this failed. I am guessing because the RGB lookup table instructions don't match my version of excel? (Clear cell contents based on color?)

Community
  • 1
  • 1
user3694057
  • 1
  • 1
    Have you tried anything else so far? – DavidG May 31 '14 at 11:46
  • I have tried the 2 macros posted, results are stated in comments box for each. – user3694057 Jun 01 '14 at 23:05
  • Happy ending, sort of? A pal at work has written a MATLAB macro that works, and after notification the person who uploaded these defective files to the genomics database source has agreed to fix the erroneous slots and reupload clean files... – user3694057 Jun 05 '14 at 19:20

2 Answers2

1

Select the area you wish to process and give this a try:

Sub ClearSome()
    Dim r As Range, rr As Range, rClear As Range
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    Set rClear = Nothing
    For Each r In rr
        If IsNumeric(r) Then
            If r.Value > 1 Then
                If rClear Is Nothing Then
                    Set rClear = r
                Else
                    Set rClear = Union(rClear, r)
                End If
            End If
        End If
    Next r

    If Not rClear Is Nothing Then
        rClear.Clear
    End If
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • This worked well up for 680 columns up to a selection of 1000 rows down. Once I pushed it to try on 3500 rows it went into spin wheel mode for over 10 mins, so I gave up, since I am looking for a solution for 50000 row files. I suspect this reflects a lack of horsepower and RAM in my Mac laptop. I will try again at work on Tuesday and a desktop PC that has 16 GB RAM. Altho the guidelines say not to, thanks! – user3694057 Jun 01 '14 at 23:00
  • Using a better PC helped partially, I was able to apply the macro to up to 3000 rows at a time, took ~30-40 secs to process (better than previous attempt on Mac). Trial and error showed if I went much beyond 3000 rows, the program went non-responsive. Also learned I had to save after each run of the macro, if I did not all prior changes were reverted to normal, or so it seemed, did not explore this in depth, just did the macro and save bit ~20 times, took about an hour total, since I have 10 more of these files, I will do this slowly... – user3694057 Jun 04 '14 at 12:16
  • Do you have many empty cells in the area being **Select** ed?? – Gary's Student Jun 04 '14 at 12:21
1

Given the size of your data it might be more efficient to read into an array and loop over the array and then write the array back to the worksheet.

Try this:

Sub RemoveValues()
    Dim values(), arrayWidth As Integer, arrayHeight As Integer, i As Integer, j As Integer

    values = Range("A1:C5") // update as per your set up
    arrayWidth = UBound(values, 2)
    arrayHeight = UBound(values, 1)

    For j = 1 To arrayHeight
        For i = 1 To arrayWidth
            If values(j, i) > 1 Then
                values(j, i) = vbNullString
            End If
        Next i
    Next j

    Range("A1").Resize(arrayHeight, arrayWidth) = values
End Sub
Alex P
  • 12,249
  • 5
  • 51
  • 70
  • I got a syntax error message with this line highlighted: values = Range("A1:WP37") // update as per your set up--I was trying to apply the macro to that range but this did not work. Am I supposed to substitute the actual width and height numbers in the first line? I did not.... – user3694057 Jun 01 '14 at 22:59
  • Let me try and have a look later. You don't need substitute the height and width numbers – Alex P Jun 02 '14 at 06:46