Print row when cell in one column blank

[Helmut]

I have the following:
Column A B C
123456781 name1 473
123456782 name2 471
123456783 name3
123456784 name4 453
123456785 name5
123456786 name6 451

The whole RANGE = "Employees"

Where C = BLANK I would like to PRINT values in A and B and quit application
without saving

 

[Joel]

try this easy code

Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Select
Selection.PrintOut

 

[Helmut]

Joel,
One further step:

If BLANK cells
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Select
Application.quit

If NO BLANK cells
Continue macro

Thanks

 

[Joel]

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set searchrange = Range("C1:C" & LastRow)

blanks = WorksheetFunction.CountBlank(searchrange)
If blanks = 0 Then Exit Sub

 

[Helmut]

Joel,
I input your suggestion like this:
------------------------------------
' Print names where DEPARTMENT CODE is missing & quit without saving

Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Select
Selection.PrintOut

lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set searchrange = Range("C1:C" & lastrow)

blanks = WorksheetFunction.CountBlank(searchrange)
If blanks = 0 Then Exit Sub

 

[Helmut]

Joel,
it's ok now, I changed :
If blanks = 0 Then Exit Sub
to
If blanks > 0 Then Application.quit

But, how can I quit WITHOUT prompt, just quit without saving?

Helmut

 

[Joel]

Try this code

------------------------------------
' Print names where DEPARTMENT CODE is missing & quit without saving
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set searchrange = Range("C1:C" & lastrow)

blanks = WorksheetFunction.CountBlank(searchrange)
If blanks > 0 Then
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Select
Selection.PrintOut
thisworkbook.close savechanges:=False
end if

 

[Helmut]

Joel,
thanks ok, but this closes: PERSONAL.XLSB and leaves the other two WORKBOOKS
open. I need to QUIT Excel completely without saving the two open workbooks.
thanks

 

[Joel]

Print Names; where; DEPARTMENT; CODE Is missing & Quit; without; saving
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set searchrange = Range("C1:C" & lastrow)

blanks = WorksheetFunction.CountBlank(searchrange)
If blanks > 0 Then
Columns("C:C").SpecialCells(xlCellTypeBlanks).EntireRow.Select
Selection.PrintOut
'close thisworkbook last
'close all other books first
For Each bk In Workbooks
If ThisWorkbook.Name <> bk.Name Then
bk.Close savechanges:=False
End If
Next bk
ThisWorkbook.Close savechanges:=False
End If

 

[Helmut]

Hi Joel,
Works great...but it is important that EXCEL QUITS at this time because I
have to reload PERSONAL.xlsb before loading any worksheets for other work,
unless we can CLOSE ALL Workbooks EXCEPT PERSONAL.xlsb.

Sorry to be such a nuisance - maybe it's my language. Helmut

 

[Joel]

I don't understand the problem. Need more description of what is not closing.

There may be a couple of diffferrent things happening. If all the workbooks
are not closing then more than one instance of excel is opened. I don't know
how more than one instance was created. usually when you open a workbook
from a macro (workbooks.open) it opens in the same instance of excel.

If the Personal.xlsb file isn't closing then maybe you are accidentally
modifying the person workbook. the personal .xlsb shouldn't be writen to.
You should then check you code to make sure you explicitly specify which
workbook you are writtting to. Alway use thisworkbook when writting/reading
the workbook with the macro. When you open or create a new workbook create a
variable that refferes to each workbook like the code below

set newbk = workbooks.add

or

set newbk = workbooks.open(filename:=abc.xls)

Then

newbk.sheets("Sheet1").range("A1") = 254

 

[Helmut]

Joel, I'll try to be more explicit:

1. I open EXCEL - it automatically loads PERSONAL.xlsb
2. I open ABC.xls and run MACRO(alef)
a. MACRO(alef) does some work on ABC.xls and opens KKK.xlsx calling
MACRO(bet)
b. MACRO(bet) contains the routine:

"Print Names; where; DEPARTMENT; CODE Is missing & Quit; without; saving"

3. If CODE is missing:
a. right now as it is written, it prints out the rows with the missing
code = o.k.
b. it closes Workbook ABC.xls - which it should
c. it closes Workbook KKK.xlsx - which it should
d. it closes Workbook PERSONAL.xlsb
i. either: it should also QUIT the application (Excel) = preferred
or: it should NOT close Workbook PERSONAL.xlsb
4. If EXCEL QUITS - then restarting EXCEL will also restart PERSONAL.xlsb
5. If EXCEL remains open to do more work, PERSONAL.xlsb is missing

Joel, I hope I have explained the situation clearer than before.
Thanks for your patience.
We're now off for Pesach for the next week, so I will be looking for your
suggested fixes and let you know how they work by the 28th or 29th.

Helmut

 

[Joel]

When I colse the workbook containing the macro it usually closes excel also.
I don't know why excel is staying open I try some tests this week and see if
I can duplicate the problem.