Check if Cells in Row are Empty

[h]

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

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

[tinyjack]

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

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

HTH

TJ

[h]

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

[tinyjack]

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

[Dave Hawley]

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.

[h]

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.....

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

[Aaron Blood]

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

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)

[h]

Thank u very much Aaron Blood

it works fantastically

yours
h