Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: VBA to find blank/non-blank cells

  1. #1
    Join Date
    4th January 2005
    Location
    Jefferson City, MO.
    Posts
    92

    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. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    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

    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.

  3. #3
    Join Date
    4th January 2005
    Location
    Jefferson City, MO.
    Posts
    92

    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.

  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    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 at 04:41. Reason: typo
    Hope that Helps

    Roy

    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.

  6. #6
    Join Date
    4th January 2005
    Location
    Jefferson City, MO.
    Posts
    92

    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.

  7. #7
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Hope that Helps

    Roy

    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.

  8. #8
    Join Date
    4th January 2005
    Location
    Jefferson City, MO.
    Posts
    92

    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.

  9. #9
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    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

    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.

  10. #10
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    19,291

    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 at 17:57.
    Hope that Helps

    Roy

    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Find Blank Cells With Number
    By excelgrrl in forum EXCEL HELP
    Replies: 3
    Last Post: March 6th, 2008, 08:34
  2. Find Sub Headings Where Blank Cells Between
    By bbromley in forum EXCEL HELP
    Replies: 5
    Last Post: December 3rd, 2007, 01:41
  3. Replies: 6
    Last Post: June 22nd, 2007, 01:30
  4. Unable to find Non-Blank Cells
    By justinlpw in forum EXCEL HELP
    Replies: 4
    Last Post: January 23rd, 2006, 10:32
  5. Replies: 1
    Last Post: December 2nd, 2004, 13:21

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno