Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Print rows of missing info
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Print rows of missing info
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.

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default Print rows of missing info
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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Print rows of missing info
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.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default Print rows of missing info
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.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Print rows of missing info
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.

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default Print rows of missing info
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.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Print rows of missing info
DomThePom,
Here is my code now. It works up to
Application.Run "PrintAndExitIfBlankEmplyeeData"
but does NOT execute it.

somehow something is wrong. Can you help? thanks here it is:
---------------------------------------------------------------------------
Sub Employees()
'
' Employees Macro
'
' Keyboard Shortcut: Ctrl+e
'

' This deletes all columns where in the row any blank cells
Rows(1).SpecialCells(xlCellTypeBlanks).EntireColum n.Delete
Rows(1).EntireRow.Delete

' Set Name "Employees" for NEW Range

With Worksheets("Employees")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lastrow).Name = "Employees"
End With

' Sort_Employees by department code in column C
'

ActiveSheet.Range("Employees").Sort _
Key1:=Columns("C"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Enter here routine for determining missing DEPARTMENT CODE

Application.Run "PrintAndExitIfBlankEmplyeeData"

' Enter Formula to Column D

Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'G:\Personel\cav index.xlsx'!accounts,2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


' Sort_Employees by SI
'
ActiveSheet.Range("Employees").Sort _
Key1:=Range("A1", "Employees"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Save Active Wrokbook and close Window

ActiveWorkbook.Save

ActiveWindow.Close

Application.Quit

End Sub
----------
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

---------------------------------------------------------------------------
"DomThePom" wrote:

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.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 54
Default Print rows of missing info
see ********* fort comments

Sub Employees()
Dim lastRow As Long
' Employees Macro
'
' Keyboard Shortcut: Ctrl+e
'

' This deletes all columns where in the row any blank cells
Rows(1).SpecialCells(xlCellTypeBlanks).EntireColum n.Delete
Rows(1).EntireRow.Delete

'******* rows (1) of what - must be specific about what you are working
on?
'*******I think this will just delete the first row of the active sheet

'Set Name "Employees" for NEW Range

With Worksheets("Employees")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lastRow).Name = "Employees"
End With

'****** probably best to use the current region range object rather than a
named range?
Dim rng As Range
Set rng = Worksheets("Employees").Cells(1, 1).CurrentRegion
'******* You can then do all sorting etc using this range

' Sort_Employees by department code in column C
'

ActiveSheet.Range("Employees").Sort _
Key1:=Columns("C"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Enter here routine for determining missing DEPARTMENT CODE

Application.Run "PrintAndExitIfBlankEmplyeeData"

'****** Just PrintAndExitIfBlankEmplyeeData
'***** no need for Application.Run or quotes


' Enter Formula to Column D

Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'G:\Personel\cav index.xlsx'!accounts,2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


'********** Can't see what this is doing
'********** Generally better not to select but to work on ranges see above

' Sort_Employees by SI
'
ActiveSheet.Range("Employees").Sort _
Key1:=Range("A1", "Employees"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Save Active Wrokbook and close Window

ActiveWorkbook.Save

ActiveWindow.Close

Application.Quit

End Sub


"Helmut" wrote:

DomThePom,
Here is my code now. It works up to
Application.Run "PrintAndExitIfBlankEmplyeeData"
but does NOT execute it.

somehow something is wrong. Can you help? thanks here it is:
---------------------------------------------------------------------------
Sub Employees()
'
' Employees Macro
'
' Keyboard Shortcut: Ctrl+e
'

' This deletes all columns where in the row any blank cells
Rows(1).SpecialCells(xlCellTypeBlanks).EntireColum n.Delete
Rows(1).EntireRow.Delete

' Set Name "Employees" for NEW Range

With Worksheets("Employees")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lastrow).Name = "Employees"
End With

' Sort_Employees by department code in column C
'

ActiveSheet.Range("Employees").Sort _
Key1:=Columns("C"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Enter here routine for determining missing DEPARTMENT CODE

Application.Run "PrintAndExitIfBlankEmplyeeData"

' Enter Formula to Column D

Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'G:\Personel\cav index.xlsx'!accounts,2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


' Sort_Employees by SI
'
ActiveSheet.Range("Employees").Sort _
Key1:=Range("A1", "Employees"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Save Active Wrokbook and close Window

ActiveWorkbook.Save

ActiveWindow.Close

Application.Quit

End Sub
----------
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

---------------------------------------------------------------------------
"DomThePom" wrote:

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.

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 111
Default Print rows of missing info
Hi DomThePom,
Sorry, but I just can't get it to work. Your comments are ok...thanks, but
I'm not that advanced in my compiling. All the other functions you commented
on are working just fine, it's the PrintAndExitIfBlankEmplyeeData which is
NOT working.
I deleted the Application.Run and the "", but it is NOT executing the
Sub PrintAndExitIfBlankEmplyeeData() and thus I don't get a print out. The
macro just stops just before the PrintAndExitIfBlankEmplyeeData statement.
Any other suggestions? And by the way thanks for the quick turnaround.
thanks
Helmut

"DomThePom" wrote:

see ********* fort comments

Sub Employees()
Dim lastRow As Long
' Employees Macro
'
' Keyboard Shortcut: Ctrl+e
'

' This deletes all columns where in the row any blank cells
Rows(1).SpecialCells(xlCellTypeBlanks).EntireColum n.Delete
Rows(1).EntireRow.Delete

'******* rows (1) of what - must be specific about what you are working
on?
'*******I think this will just delete the first row of the active sheet

'Set Name "Employees" for NEW Range

With Worksheets("Employees")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lastRow).Name = "Employees"
End With

'****** probably best to use the current region range object rather than a
named range?
Dim rng As Range
Set rng = Worksheets("Employees").Cells(1, 1).CurrentRegion
'******* You can then do all sorting etc using this range

' Sort_Employees by department code in column C
'

ActiveSheet.Range("Employees").Sort _
Key1:=Columns("C"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Enter here routine for determining missing DEPARTMENT CODE

Application.Run "PrintAndExitIfBlankEmplyeeData"

'****** Just PrintAndExitIfBlankEmplyeeData
'***** no need for Application.Run or quotes


' Enter Formula to Column D

Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'G:\Personel\cav index.xlsx'!accounts,2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


'********** Can't see what this is doing
'********** Generally better not to select but to work on ranges see above

' Sort_Employees by SI
'
ActiveSheet.Range("Employees").Sort _
Key1:=Range("A1", "Employees"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Save Active Wrokbook and close Window

ActiveWorkbook.Save

ActiveWindow.Close

Application.Quit

End Sub


"Helmut" wrote:

DomThePom,
Here is my code now. It works up to
Application.Run "PrintAndExitIfBlankEmplyeeData"
but does NOT execute it.

somehow something is wrong. Can you help? thanks here it is:
---------------------------------------------------------------------------
Sub Employees()
'
' Employees Macro
'
' Keyboard Shortcut: Ctrl+e
'

' This deletes all columns where in the row any blank cells
Rows(1).SpecialCells(xlCellTypeBlanks).EntireColum n.Delete
Rows(1).EntireRow.Delete

' Set Name "Employees" for NEW Range

With Worksheets("Employees")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lastrow).Name = "Employees"
End With

' Sort_Employees by department code in column C
'

ActiveSheet.Range("Employees").Sort _
Key1:=Columns("C"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Enter here routine for determining missing DEPARTMENT CODE

Application.Run "PrintAndExitIfBlankEmplyeeData"

' Enter Formula to Column D

Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'G:\Personel\cav index.xlsx'!accounts,2)"
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


' Sort_Employees by SI
'
ActiveSheet.Range("Employees").Sort _
Key1:=Range("A1", "Employees"), _
Order1:=xlAscending, _
DataOption1:=xlSortNormal, _
Header:=xlNo

' Save Active Wrokbook and close Window

ActiveWorkbook.Save

ActiveWindow.Close

Application.Quit

End Sub
----------
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

---------------------------------------------------------------------------
"DomThePom" wrote:

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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Missing info on worksheet BCalaway Excel Worksheet Functions 0 April 27th 09 04:07 PM
Missing info in Formula Tab SOS Excel 2007 Excel Discussion (Misc queries) 6 September 29th 07 04:19 PM
Legend Missing Info Debra Ann Charts and Charting in Excel 0 October 11th 05 03:23 PM
VBA Challenge - Flag Missing Info keepITcool Excel Programming 0 July 21st 04 09:44 PM
VBA Challenge - Flag Missing Info Frank Kabel Excel Programming 0 July 21st 04 08:04 PM


All times are GMT +1. The time now is 09:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement