Print Dialogue by Hiding Empty Rows

[adam]

Hi anyone,

I'm using the following code to hide rows if the column F is empty and open the print dialogue. The code works fine if the last row(column F37 is empty) but if F37 is filled with data I get the debug message.

How may I prevent this? I have formulas in column H,K,M & O

Any help on this would be kindly appreciated.

Code: Select all

Sub PrintDialogByHidingEmptyRows()
Application.ScreenUpdating = False
Worksheets("MySheet").Range("F18:F37").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Application.Dialogs(xlDialogPrint).Show
Application.ScreenUpdating = True
End Sub

[HansV]

Create a loop:

Code: Select all

  Dim r As Long
  For r = 18 To 37
    With Worksheets("MySheet").Range("F" & r)
      If .Value = "" Then
        .EntireRow.Hidden = True
      End If
    End With
  Next r

[adam]

Thanks for the help Hans. I do really appreciate it.

[adam]

At times when the user accidentally clicks the print macro without filling a single column between the rows F18 to F37; all the rows get hidden and the sheet gets printed.

How could a message that says "you must fill at least one field to print the sheet be created with such a scenario".

Any help on this would be kindly appreciated.

[HansV]

Insert the following above the For ... loop:

Code: Select all

  If Application.WorksheetFunction.CountA(Worksheets("MySheet").Range("F18:F37")) = 0 Then
    MsgBox "you must fill at least one field to print the sheet be created with such a scenario", vbInformation
    Exit Sub
  End If

[adam]

Thanks for the help Hans. The code works fine now. I do really appreciate your help.

[adam]

If I want the Worksheet to be the active sheet how should I change the line If Application.WorksheetFunction.CountA(Worksheets("MySheet").Range("F18:F37")) = 0 Then?

Any help would be kindly appreciated.

Thanks in advance.

[adam]

I've got it solved. the caption of the message box says Microsoft excel. Instead of this how could I make a message box where the caption appears as my workbook title?

[HansV]

MsgBox "You must fill at least one field to print the sheet be created with such a scenario", vbInformation, ActiveWorkbook.Name

[adam]

Thanks for the help Hans. But is there a way I could avoid the .xlsm part appearing in the message box caption.

[HansV]

Yes. Use the InStr and Left functions to extract the part before the point from the name,

[adam]

Thanks for the reply Hans. How could I change the caption of the of the message box that appears when I click the excel close button?

[HansV]

That message box is a built-in Excel message box, not a VBA message box. You cannot change its caption.

[adam]

Thanks for the reply Hans.