Search A Spreadsheet For A string Value and clear contents in the 3 cells above it

[israpound]

Any help would be appreciated on this as I am not an Excel\VBA programmer. I use SPSS daily at work which frequently results in generating excel SS's.
I have a large table with cell counts of less than 5 and SPSS allows one to generate an excel table with the following text if the cell count is below five "<5".
All I need to do is find each cell containing <5 and simply clear the contents of the 3 cells above it and leave the "<5" string in place.

For what its worth (trying not to be too lazy)I managed to figure out the code to insert a blank line after "Totals" in the table but this issue with "<5"
is too perplexing. Any help would really be appreciated.

 

[Fishboy]

Any help would be appreciated on this as I am not an Excel\VBA programmer. I use SPSS daily at work which frequently results in generating excel SS's.
I have a large table with cell counts of less than 5 and SPSS allows one to generate an excel table with the following text if the cell count is below five "<5".
All I need to do is find each cell containing <5 and simply clear the contents of the 3 cells above it and leave the "<5" string in place.

For what its worth (trying not to be too lazy)I managed to figure out the code to insert a blank line after "Totals" in the table but this issue with "<5"
is too perplexing. Any help would really be appreciated.

Hi israpound, welcome to the boards.

Assuming that we are talking about column A (if not just update each instance of the "A" reference in my code), I think this should do the trick:

Sub ClearSpecificCells()
Dim Cell As Range, cRange As Range
    LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
        Set cRange = Range("A1:A" & LastRow)
            For Each Cell In cRange
                If Cell.Value = "<5" Then
                    Cell.Offset(-3, 0).ClearContents
                End If
            Next Cell
End Sub

 

[israpound]

Thank you for your response....I need to consider the whole SS so I am guessing adjust the range, for example as such Range("A1:Z100"...

Thanks Again

 

[Fishboy]

Thank you for your response....I need to consider the whole SS so I am guessing adjust the range, for example as such Range("A1:Z100"...

Thanks Again

Referencing each cell of the sheet may take a little longer to process, but you could change it as follows to only check the cells which have actually been used:

Sub ClearSpecificCells()
Dim Cell As Range
            For Each Cell In ActiveSheet.UsedRange
                If Cell.Value = "<5" Then
                    Cell.Offset(-3, 0).ClearContents
                End If
            Next Cell
End Sub

 

[israpound]

This is a sample bmp of the SS. I didn't get an error message using the code, however it did not clear the cells. Any thoughts?

 

[israpound]

The picture did not show up. Anyhow, thanks. If you can still assist that would be great. I think I need to be clearer about the structure of the SS. I have a SS ranged as A1:X286 and just imagine a column, with "<5" in cell C5 and C1 is blank, C2 has 75.0, C3 may be blank and C4 has 25.0 in it. This condition can be anywhere in the range. So I am looking for code to find "<5" and clear the 4 cells above. Any help will be really appreciated. Thanks...

 

[JoeMo]

This is a sample bmp of the SS. I didn't get an error message using the code, however it did not clear the cells. Any thoughts?

Can you post a sample of your SS using one of the two tools below? I'm guessing you may have some non-visible characters in those cells containing "<5".

Excel Jeanie link: Download
MrExcel HTML Maker link: http://www.mrexcel.com/forum/2545970-post2.html

 

[israpound]

I can't get either utility to work, likely because I am using "Excel 2013". I do know that the cells containing "<5" are formatted as "General".

 

[JoeMo]

I can't get either utility to work, likely because I am using "Excel 2013". I do know that the cells containing "<5" are formatted as "General".

Pick a cell containing "<5" let's say A1 for example. In any empty cell enter: =LEN(A1)

using the correct cell address of course. What does that formula return?

 

[Rick Rothstein]

As long as there are no other cells containing the two characters "<5" except the ones you are interested in, then give this macro a try (it will straighten out any cells with extraneous, invisible characters in it)...

Sub ClearAboveCellsWithLessThanFive()
  Dim Cell As Range, CellsToClearCount As Long
  CellsToClearCount = 4
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    .Replace "*<5*", "#N/A", xlWhole
    For Each Cell In .SpecialCells(xlConstants, xlErrors)
      Cell.Offset(-CellsToClearCount).Resize(CellsToClearCount).ClearContents
      Cell.Value = "<5"
    Next
  End With
  Application.ScreenUpdating = True
End Sub