Locate blank cells in the column:

[polavar]

In my spread sheet the Used columns are from 'A' to 'T'.

Out of those columns few columns should not have blank cells.

For Eg: A, B, C, D, H, I, K, N coulmns should not have blank cells.

If there is any blank cell in those columns for eg in 'A' column then, I should get msg box saying 'There are blank cells in 'A' column'

Can any body help me.

Thank You

 

[SkipVought]

Hi,

Here's a shot

Sub test()
    MsgBox HowManyBlanks, , "Blank cells in columns"
End Sub
Function HowManyBlanks() As String
    Dim sOut As String, nbr As Integer
    sOut = ""
    For icol = 1 To Range("T:T").Column
        nbr = Application.CountIf(Range(Cells(1, icol), Cells(LastRow(ActiveSheet), icol)), "")
        If nbr > 0 Then
            sOut = sOut & Chr(icol + 64) & ":"
            sOut = sOut & nbr & "; "
        End If
    Next
    HowManyBlanks = sOut
End Function
Function LastRow(AWorksheet As Worksheet) As Long
'thanks to Zathras, Expert contributor at Tek-Tips.com
' Generic function to find the last actually used row
' in a worksheet.  "UsedRange" is taken for the first
' approximation.  Then rows are then tested for the
' presence of actual data with the CountA function.
' Rows that only have formatting, colors, patterns,
' borders, etc. are ignored.
' Returns zero if there is no data in the sheet
' or if there is invalid input.

Dim bCheckingForBadUsedRange As Boolean
  If AWorksheet Is Nothing Then
    MsgBox "LastRow function called without a valid Worksheet object."
    LastRow = 0
  Else
    With AWorksheet.UsedRange
      LastRow = .Rows.Count + .Row - 1
    End With
    bCheckingForBadUsedRange = True
    While bCheckingForBadUsedRange And LastRow > 0
      If LastRow > 0 And WorksheetFunction.CountA(Cells(LastRow, 1).EntireRow) = 0 Then
        LastRow = LastRow - 1
      Else
        bCheckingForBadUsedRange = False
      End If
    Wend
  End If
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com