Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Check if Cells in Row are Empty

  1. #1
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

    Check if Cells in Row are Empty

    Hi

    how to check if all the cells in the EntirRow of the activecell are empty ?
    if so then msgbox then exit sub......else proceeds the rest macro
    i want the mzgbox appear when the check is finished and the condition is met

    I have this try, but it did not work

    VB:
    Sub Macro1() 
        Dim c As Range 
        For Each c In ActiveCell.Cells(ActiveCell, 32).EntireRow 
            If isempty(c.Value) Then 
                 
            End If 
        Next 
        MsgBox "Check Your Analysis Distribution ", 16, "   Wrong Distribution" 
        Exit Sub 
         
         '''my rest macro
         
    End Sub 
    
    
    Does it realy need Loop structure or there is other way ?

    yours
    h

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    6th September 2004
    Posts
    438

    Re: check if cells in EntirRow are empty

    You could use Counta if you are not worried about formats and comments.

    VB:
    If Application.CountA(ActiveCell.EntireRow)=0 Then 
        MsgBox "Check Your Analysis Distribution ", 16, "   Wrong Distribution" 
        Exit Sub 
    End If 
    
    
    HTH

    TJ
    Oh dear I need a beer
    Online Motorsport Game

  3. #3
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

    Re: check if cells in EntirRow are empty

    Thanks tinyjack..but it did not work

    still the entire row of the activecell when all cells in it are empty, the msg does not fire.........

    any help is appreciated

    h

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    6th September 2004
    Posts
    438

    Re: check if cells in EntirRow are empty

    It all depends on your definition of empty. Any of these will cause the my code to think that the row is not empty:

    A cell containing just a space character
    A cell containing 0 that has been formatted to display a blank
    A cell containing a formula that returns ""

    These will all result in a row that looks empty but that is not.

    If you place the cursor in the A column of the row in question and check that it is empty (Press F2 to ensure that the cell does not contain just spaces) and then press End and then the Right Arrow, it will take you to the cell that is causing the macro to fail.

    Let us know what you find.

    TJ
    Oh dear I need a beer
    Online Motorsport Game

  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: check if cells in EntirRow are empty

    still the entire row of the activecell when all cells in it are empty
    That's because at least one cell is not empty. Any cell that houses any data or any formula can never be empty.

  6. #6
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

    Re: Check if Cells in Row are Empty

    Thank u 4 your help..However........

    Ok there is a fomula in col B of the row in question,
    so i need then to check all the cells from the col C to AA of the row in question,
    So if all of these cells are empty, then fire msg,
    and else exit sub.....

    VB:
    Sub Macro1() 
        Dim c As Range 
        For Each c In ActiveCell.Cells(ActiveCell, 32).EntireRow 
            If IsEmpty(c.Value) Then 
                 
            End If 
        Next 
        MsgBox "Check Your Analysis Distribution ", 16, "   Wrong Distribution" 
        Exit Sub 
         
         '''my rest macro
    End Sub 
    
    
    yours
    h

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: Check if Cells in Row are Empty

    Something like this should work... and it avoids the loop structure.

    VB:
    Dim neValues As Range, neFormulas As Range, MyRange As Range 
     
    Set MyRange = Columns("C:AA") 
     
    On Error Resume Next 
    Set neValues = Intersect(ActiveCell.EntireRow.SpecialCells(xlConstants), MyRange) 
    Set neFormulas = Intersect(ActiveCell.EntireRow.SpecialCells(xlFormulas), MyRange) 
    On Error Goto 0 
     
    If neValues Is Nothing And neFormulas Is Nothing Then 
        MsgBox "Nothing There" 
    Else 
        MsgBox "Something's There" 
    End If 
    
    

    They should've made it so you can combine the type constants, same as value constants.

    Something like:
    .SpecialCells(xlFormulas + xlConstants)
    Sub All_Macros(Optional control As Variant)

  8. #8
    Join Date
    7th May 2004
    Location
    Libya
    Posts
    619

    Re: Check if Cells in Row are Empty

    Thank u very much Aaron Blood

    it works fantastically

    yours
    h

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 3
    Last Post: December 5th, 2008, 08:46
  2. Check For Empty Cells In Non Contiguous Range
    By pete_l in forum EXCEL HELP
    Replies: 3
    Last Post: August 13th, 2008, 08:49
  3. Make Formula Cells Empty Rather Than Empty Text
    By Kadence in forum EXCEL HELP
    Replies: 3
    Last Post: April 17th, 2008, 13:17
  4. If Statement To Check If Cells Are Empty
    By jwewing23 in forum EXCEL HELP
    Replies: 8
    Last Post: March 11th, 2008, 07:53
  5. Workbook Macro To Check Empty Cells
    By rileysan in forum Excel and/or Word Help
    Replies: 2
    Last Post: October 24th, 2007, 03:45

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