Announcement

Collapse
No announcement yet.

VBA to find blank/non-blank cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA to find blank/non-blank cells

    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.
    Few things in life are better than helping someone.

  • #2
    Re: VBA to find blank/non-blank cells

    Is this what you mean?
    Code:
    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
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: VBA to find blank/non-blank cells

      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,
      Few things in life are better than helping someone.

      Comment


      • #4
        Re: VBA to find blank/non-blank cells

        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

        Comment


        • #5
          Re: VBA to find blank/non-blank cells

          Does this work?
          Code:
          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
          Last edited by royUK; August 7th, 2005, 03:41. Reason: typo
          Hope that Helps

          Roy

          New users should read the Forum Rules before posting

          For free Excel tools & articles visit my web site

          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

          RoyUK's Web Site

          royUK's Database Form

          Where to paste code from the Forum

          About me.

          Comment


          • #6
            Re: VBA to find blank/non-blank cells

            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.

            Code:
            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.
            Few things in life are better than helping someone.

            Comment


            • #7
              Re: VBA to find blank/non-blank cells

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

              Roy

              New users should read the Forum Rules before posting

              For free Excel tools & articles visit my web site

              If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

              RoyUK's Web Site

              royUK's Database Form

              Where to paste code from the Forum

              About me.

              Comment


              • #8
                Re: VBA to find blank/non-blank cells

                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,
                Few things in life are better than helping someone.

                Comment


                • #9
                  Re: VBA to find blank/non-blank cells

                  try this amendment:
                  Code:
                  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
                  Hope that Helps

                  Roy

                  New users should read the Forum Rules before posting

                  For free Excel tools & articles visit my web site

                  If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                  RoyUK's Web Site

                  royUK's Database Form

                  Where to paste code from the Forum

                  About me.

                  Comment


                  • #10
                    Re: VBA to find blank/non-blank cells

                    Slight adjustment to code:
                    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
                    Last edited by royUK; August 7th, 2005, 16:57.
                    Hope that Helps

                    Roy

                    New users should read the Forum Rules before posting

                    For free Excel tools & articles visit my web site

                    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                    RoyUK's Web Site

                    royUK's Database Form

                    Where to paste code from the Forum

                    About me.

                    Comment


                    • #11
                      Re: VBA to find blank/non-blank cells

                      Getting real close, Thank you so much for your time. Here is the worksheet I'm trying to do this to. If you click on the button it will run your macro, the only change I made was changing 15 to 41. If you look at before running the macro you will see that there is text in some of the column A cells, after you run the macro you will notice that some of them are hidden and the rows with all "xxx" some are still visible.

                      But again I thank you for your time.
                      Attached Files
                      Few things in life are better than helping someone.

                      Comment


                      • #12
                        Re: VBA to find blank/non-blank cells

                        have you used the amended code that I posted? I will download you wb & take a look.
                        Hope that Helps

                        Roy

                        New users should read the Forum Rules before posting

                        For free Excel tools & articles visit my web site

                        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                        RoyUK's Web Site

                        royUK's Database Form

                        Where to paste code from the Forum

                        About me.

                        Comment


                        • #13
                          Re: VBA to find blank/non-blank cells

                          I had spotted that error and I think the amended code will work right noe
                          Code:
                          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 41
                                  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 'missed before
                                  Else: cnt = 0
                                  End If
                              Next i
                          
                          End Sub
                          Last edited by royUK; August 7th, 2005, 18:03.
                          Hope that Helps

                          Roy

                          New users should read the Forum Rules before posting

                          For free Excel tools & articles visit my web site

                          If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                          RoyUK's Web Site

                          royUK's Database Form

                          Where to paste code from the Forum

                          About me.

                          Comment


                          • #14
                            Re: VBA to find blank/non-blank cells

                            You're right, thank you so much the macro works perfectly. Your time and effort on this is greatly appreciated.
                            Few things in life are better than helping someone.

                            Comment

                            Working...
                            X