Hi,
I have a RANGE "Employees" with some missing info:

Column A Columns B Column C
21823059 גדעון לי×*×� 474
27640374 מיע�רי ט�רק 474
36095008 מור×�×*×™ רי×� 474
15786791 פוקס מיכל blank cell
27820257 הר�ל שלומציון blank cell
28488567 ×�ר×*פרוי×*ד רו×*ית blank cell

I would like that IF NO blank cells in Range THEN continue MACRO

but IF blank cells in Range THEN "print" value of cells A and B and Exit
both Worksheet and Excel.

Here you go Helmut:

Sub PrintAndExitIfBlankEmplyeeData()

Dim rng As Range
Set rng = Range("Employees")
If rng.SpecialCells(xlCellTypeBlanks).Cells.Count < 0 Then
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

"Helmut" wrote:

Hi,
I have a RANGE "Employees" with some missing info:

Column A Columns B Column C
21823059 גדעון לי×*×� 474
27640374 מיע�רי ט�רק 474
36095008 מור×�×*×™ רי×� 474
15786791 פוקס מיכל blank cell
27820257 הר�ל שלומציון blank cell
28488567 ×�ר×*פרוי×*ד רו×*ית blank cell

I would like that IF NO blank cells in Range THEN continue MACRO

but IF blank cells in Range THEN "print" value of cells A and B and Exit
both Worksheet and Excel.

Hi DomThePom,
Great BUT it prints not only the rows with the BLANK CELL but it prints the
whole worksheet. I only want to print the rows with the blank cells.
thanks
Helmut

"DomThePom" wrote:

Here you go Helmut:

Sub PrintAndExitIfBlankEmplyeeData()

Dim rng As Range
Set rng = Range("Employees")
If rng.SpecialCells(xlCellTypeBlanks).Cells.Count < 0 Then
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

"Helmut" wrote:

Hi,
I have a RANGE "Employees" with some missing info:

Column A Columns B Column C
21823059 גדעון לי×*×� 474
27640374 מיע�רי ט�רק 474
36095008 מור×�×*×™ רי×� 474
15786791 פוקס מיכל blank cell
27820257 הר�ל שלומציון blank cell
28488567 ×�ר×*פרוי×*ד רו×*ית blank cell

I would like that IF NO blank cells in Range THEN continue MACRO

but IF blank cells in Range THEN "print" value of cells A and B and Exit
both Worksheet and Excel.

Ask and ye......

Sub PrintAndExitIfBlankEmplyeeData()
Dim rngRow As Range
Dim rng As Range
Set rng = Range("Employees")
If CountBlanksCells(rng) < 0 Then

'hide complete rows
For Each rngRow In rng.Rows
'exclude first row
If rngRow.row < 1 Then
If CountBlanksCells(rngRow) = 0 Then
rngRow.EntireRow.Hidden = True
End If
End If
Next rngRow
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

Function CountBlanksCells(ByRef rng As Range) As Long
Dim lngCount As Long
On Error Resume Next
lngCount = rng.SpecialCells(xlCellTypeBlanks).Cells.Count
If Err.Number = 0 Then
CountBlanksCells = lngCount
Else
CountBlanksCells = 0
End If
On Error GoTo 0
End Function

"Helmut" wrote:

Hi DomThePom,
Great BUT it prints not only the rows with the BLANK CELL but it prints the
whole worksheet. I only want to print the rows with the blank cells.
thanks
Helmut

"DomThePom" wrote:

Here you go Helmut:

Sub PrintAndExitIfBlankEmplyeeData()

Dim rng As Range
Set rng = Range("Employees")
If rng.SpecialCells(xlCellTypeBlanks).Cells.Count < 0 Then
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

"Helmut" wrote:

Hi,
I have a RANGE "Employees" with some missing info:

Column A Columns B Column C
21823059 גדעון לי×*×� 474
27640374 מיע�רי ט�רק 474
36095008 מור×�×*×™ רי×� 474
15786791 פוקס מיכל blank cell
27820257 הר�ל שלומציון blank cell
28488567 ×�ר×*פרוי×*ד רו×*ית blank cell

I would like that IF NO blank cells in Range THEN continue MACRO

but IF blank cells in Range THEN "print" value of cells A and B and Exit
both Worksheet and Excel.

Hi,
I am getting a "compile error" : Sub or Function not defined against:

If CountBlanksCells(rng) < 0 Then

Help please... thanks
Helmut
"DomThePom" wrote:

Ask and ye......

Sub PrintAndExitIfBlankEmplyeeData()
Dim rngRow As Range
Dim rng As Range
Set rng = Range("Employees")
If CountBlanksCells(rng) < 0 Then

'hide complete rows
For Each rngRow In rng.Rows
'exclude first row
If rngRow.row < 1 Then
If CountBlanksCells(rngRow) = 0 Then
rngRow.EntireRow.Hidden = True
End If
End If
Next rngRow
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

Function CountBlanksCells(ByRef rng As Range) As Long
Dim lngCount As Long
On Error Resume Next
lngCount = rng.SpecialCells(xlCellTypeBlanks).Cells.Count
If Err.Number = 0 Then
CountBlanksCells = lngCount
Else
CountBlanksCells = 0
End If
On Error GoTo 0
End Function

"Helmut" wrote:

Hi DomThePom,
Great BUT it prints not only the rows with the BLANK CELL but it prints the
whole worksheet. I only want to print the rows with the blank cells.
thanks
Helmut

"DomThePom" wrote:

Here you go Helmut:

Sub PrintAndExitIfBlankEmplyeeData()

Dim rng As Range
Set rng = Range("Employees")
If rng.SpecialCells(xlCellTypeBlanks).Cells.Count < 0 Then
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

"Helmut" wrote:

Hi,
I have a RANGE "Employees" with some missing info:

Column A Columns B Column C
21823059 גדעון לי×*×� 474
27640374 מיע�רי ט�רק 474
36095008 מור×�×*×™ רי×� 474
15786791 פוקס מיכל blank cell
27820257 הר�ל שלומציון blank cell
28488567 ×�ר×*פרוי×*ד רו×*ית blank cell

I would like that IF NO blank cells in Range THEN continue MACRO

but IF blank cells in Range THEN "print" value of cells A and B and Exit
both Worksheet and Excel.

I have just copied exactly what was in the post to a blank module, removed
the ''s and not got any compile errors.

Are you sure that you pasted both the:

Sub PrintAndExitIfBlankEmplyeeData

and the:

Function CountBlanksCells

into your mudule ?

Clearly, if you pasted the sub and not the function then you will get a
compile error!

"Helmut" wrote:

Hi,
I am getting a "compile error" : Sub or Function not defined against:

If CountBlanksCells(rng) < 0 Then

Help please... thanks
Helmut
"DomThePom" wrote:

Ask and ye......

Sub PrintAndExitIfBlankEmplyeeData()
Dim rngRow As Range
Dim rng As Range
Set rng = Range("Employees")
If CountBlanksCells(rng) < 0 Then

'hide complete rows
For Each rngRow In rng.Rows
'exclude first row
If rngRow.row < 1 Then
If CountBlanksCells(rngRow) = 0 Then
rngRow.EntireRow.Hidden = True
End If
End If
Next rngRow
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

Function CountBlanksCells(ByRef rng As Range) As Long
Dim lngCount As Long
On Error Resume Next
lngCount = rng.SpecialCells(xlCellTypeBlanks).Cells.Count
If Err.Number = 0 Then
CountBlanksCells = lngCount
Else
CountBlanksCells = 0
End If
On Error GoTo 0
End Function

"Helmut" wrote:

Hi DomThePom,
Great BUT it prints not only the rows with the BLANK CELL but it prints the
whole worksheet. I only want to print the rows with the blank cells.
thanks
Helmut

"DomThePom" wrote:

Here you go Helmut:

Sub PrintAndExitIfBlankEmplyeeData()

Dim rng As Range
Set rng = Range("Employees")
If rng.SpecialCells(xlCellTypeBlanks).Cells.Count < 0 Then
Range("Employees").Resize(, 2).PrintOut
Application.DisplayAlerts = False
Application.Quit
End If

End Sub

"Helmut" wrote:

Hi,
I have a RANGE "Employees" with some missing info:

Column A Columns B Column C
21823059 גדעון לי×*×� 474
27640374 מיע�רי ט�רק 474
36095008 מור×�×*×™ רי×� 474
15786791 פוקס מיכל blank cell
27820257 הר�ל שלומציון blank cell
28488567 ×�ר×*פרוי×*ד רו×*ית blank cell

I would like that IF NO blank cells in Range THEN continue MACRO

but IF blank cells in Range THEN "print" value of cells A and B and Exit
both Worksheet and Excel.