VBA to find blank/non-blank cells

[viper]

What I'm trying to accomplish is this:

I've got a range of cells A3:G124, I am looking for a way to check A3 if it's value is equal to xxx then check B3, if xxx check C3, all the way to G3, if A3:G3 have xxx as their value then hide that row, then move to the next row and check A4:G4, same as above if all equal xxx then hide row. If any cell in the row does not equal xxx then move to the next row. The reason for this is I have so many cells and I want to hide the rows without real text.

[royUK]

Is this what you mean?

Option Explicit 
 
Sub HideRows() 
    Dim MyRange As Range, cl As Range 
    Set MyRange = Range("a4").CurrentRegion 
    For Each cl In MyRange 
        If cl.Value = "xxx" Then 
            cl.EntireRow.Select 
            Selection.EntireRow.Hidden = True 
        End If 
    Next cl 
     
End Sub 

[viper]

Thanks Roy,

It works great but it does the opposite of what I'm looking for. Sorry if the explaination isn't clear. I only need the row hidden if every cell in the row A3:G3 has the value "xxx", if even one cell doesn't have "xxx" as it's value then don't hide that row and move to the next row.

Thanks again,

[Jack in the UK]

Viper
You will need a powerful UDF for this and a maker to be classed as a hit - then hide on demand if that kind of makes sence

jiuk

[royUK]

Does this work?

Sub HideRows() 
    Dim MyRange As Range, cl As Range 
    Dim x As String 
    Dim i As Integer 
    i = 1 
    x = "xxx" 
    For i = 1 To 124 
        Set MyRange = Range("a" & i, Range("g" & i)) 
        With MyRange 
            Set  cl = .Find(x, LookIn:=xlValues) 
            If Not cl Is Nothing Then 
                Range("a" & i).EntireRow.Hidden = True 
            End If 
        End With 
    Next i 
End Sub 

[viper]

Thanks but it still hid all the rows with data in them in columns other than A. Can I use Count or Countif?

I'm not sure how to write it but something like this.

Dim i As Integer, co As Integer, myrange As Range 
Set i = 3 To 124 'specifies rows to check
Set co = 1 To 7 'specifies columns A to G
 
myrange = Range(coi) 'supposed to reference Column-Row
 
For Each c In myrange 
    If Countif(c.value,"xxx") = 7 Then 'only hide if all cells from A:G have "xxx" as value
        c.Select 
        With Selection 
            .EnitreRow.Hidden = True 
        End With 
    Else 
        Move down one row 
    End If 
Next c 

I know the above code is probably an embarrassment to my code writing but I have no clue how to get it to do what I want.

Jack, using a UDF might be an idea I can work on if nothing else works.

Thanks to both of you for your time, I really appreciate it.

[royUK]

It seems to work for me if I understand what you want. If any cell in a row contains xxx that row is hidden.

[viper]

You're right Roy, the code works perfectly, excellent work.
What I'm looking for is this:

MyRange is A3:G124

I have data in range(A3:G3) down to row 124. What I'm trying to do is hide only those rows that every cell has "xxx" as it's value. So if A3 has "Roy", B3 has "Jack", C3:G3 have "xxx", do not hide because all cells in the row from A3 to G3 do not have "xxx" as their value so check row 4, now if A4:G4 all have "xxx" as their value then hide row 4 and move to row 5. If A5 has "xxx", B5 has "xxx", and C5 has "viper", D5:G5 have "xxx" still do not hide because all the cells don't have "xxx" as their value (C5 has "viper"), move to row 6 and check those cells until reach row 124.

I'm sorry that maybe my explinations are inadequate, I can see what I want and need the code to do but cannot write it.

Thanks,

[royUK]

try this amendment:

Option Explicit 
 
Sub HideRows() 
    Dim myrange As Range, cl As Range 
    Dim x As String 
    Dim i As Integer, cnt As Integer 
    i = 1 
    x = "xxx" 
    For i = 1 To 15 
        Set myrange = Range("a" & i, Range("g" & i)) 
        For Each cl In myrange 
            If cl.Value = x Then 
                cnt = cnt + 1 
            End If 
        Next cl 
        If cnt = 7 Then 
            Range("a" & i).EntireRow.Hidden = True 
        Else: cnt = 0 
        End If 
    Next i 
End Sub 

[royUK]

Slight adjustment to code:

 
 
Option Explicit 
 
Sub HideRows() 
    Dim myrange As Range, cl As Range 
    Dim x As String 
    Dim i As Integer, cnt As Integer 
     
    x = "xxx" 
    cnt = 0 
    Application.ScreenUpdating = False 
    For i = 1 To 15 
        Set myrange = Range("a" & i, Range("g" & i)) 
        For Each cl In myrange 
            If cl.Value = x Then 
                cnt = cnt + 1 
            End If 
        Next cl 
        If cnt = 7 Then 
            Range("a" & i).EntireRow.Hidden = True 
            cnt = 0 'misses this in last post
        Else: cnt = 0 
        End If 
    Next i 
    Application.ScreenUpdating = True 
End Sub