Announcement

Collapse
No announcement yet.

Check if Cells in Row are Empty

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

  • 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

    Code:
    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
    ******************
    Yours
    h

    Your Help Is Highly Appreciated

  • #2
    Re: check if cells in EntirRow are empty

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

    Code:
    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

    Comment


    • #3
      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
      ******************
      Yours
      h

      Your Help Is Highly Appreciated

      Comment


      • #4
        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

        Comment


        • #5
          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.

          Comment


          • #6
            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.....

            Code:
            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
            ******************
            Yours
            h

            Your Help Is Highly Appreciated

            Comment


            • #7
              Re: Check if Cells in Row are Empty

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

              Code:
              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)

              Comment


              • #8
                Re: Check if Cells in Row are Empty

                Thank u very much Aaron Blood

                it works fantastically

                yours
                h
                ******************
                Yours
                h

                Your Help Is Highly Appreciated

                Comment

                Working...
                X