Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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
    18,505

    Re: VBA to find blank/non-blank cells

    Is this what you mean?
    VB:
    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
    18,505

    Re: VBA to find blank/non-blank cells

    Does this work?
    VB:
    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.

    VB:
    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
    18,505

    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
    18,505

    Re: VBA to find blank/non-blank cells

    try this amendment:
    VB:
    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
    18,505

    Re: VBA to find blank/non-blank cells

    Slight adjustment to code:
    VB:
     
     
    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