SpecialCells(xlCellTypeBlanks) Not to Delete Rows

[Cincy Fan]

Greetings Excel Freinds

Excel 2003 - Windows XP
No Formal VBA Training
Learned with Macro Recorder and Mr. Excel.com

After looking at several posts for xlCellTypeBlanks most seem to be about deleting Rows.

I'm wanting to use xlCellTypeBlanks to verify data integrity

Searching a Range for Blanks, if there is a Blank, a Record is either missing or incomplete.

I want to get the Date in Column A of each ROW that has a Blank and Run the Procedure to pull that dates records.

Test Data, Range("C200:F232"), has 25 Blank Cells, on 15 Different Rows

Three Issues I'm having:
rIncompleteRecords.Rows.Count is returning 1 not 15
and
For Each Row (with Blanks) How do I get
Range("A" & rIncompleteRecords.Row).Value?
I need each row not each Cell as the called Procedure will get the data for all cells in that Row
Although it wouldn't hurt to know how to do the For Each Cell process as well.
and (Two and a Half) Issues
No Blanks, Data are OK exit without error. (I have find this issue and do not "need" it, Just thought it would be nice to have all of this in one Place for Future reference).

Code Snipet:

Sub FindMissingRecords()
   Dim rIncompleteRecords As Range
   Dim i As Integer
 
   Set rIncompleteRecords = _
   Sheets("Fiscal Data").Range("C200:F232").SpecialCells(xlCellTypeBlanks)
 
   MsgBox rIncompleteRecords.Rows.Count, vbOKOnly, "Number of Different Rows"
    'Only returning 1
 
   For i = 1 To rIncompleteRecords.Rows.Count
'Need .Value (a Date) from Column "A"  of each missing or Incomplete Row.
      'Get Missing Records Here 
      '*** Procedure Working if Date is provided***
      'Need to change ActiveCell.Value to
         Range("A" & rIncompleteRecords.Row).Value ***HELP HERE *** 
      GetSalesDate (ActiveCell.Value)
   Next i
End Sub

Thank You in advance.

 

[xenou]

Sub FindMissingRecords()
Dim r As Range, sAllRows As String
sAllRows = "&"
On Error Resume Next
For Each r In [Fiscal Data!C200:F232].SpecialCells(4)
If InStr(sAllRows, "&" & r.Row & "&") = 0 Then
'do your stuff - it's a new row
'add the row number
sAllRows = sAllRows & r.Row & "&"
End If
Next
End Sub

Neat idea. Thanks, wigi.

 

[Cincy Fan]

Thank You WIGI

Your code is Very Nice

Sub FindMissingRecords()
    Dim r As Range, sAllRows As String
    sAllRows = "&"
    On Error Resume Next
    For Each r In [Fiscal Data!C200:F232].SpecialCells(4)
        If InStr(sAllRows, "&" & r.Row & "&") = 0 Then
            'do your stuff - it's a new row
            'add the row number
            sAllRows = sAllRows & r.Row & "&"
        End If
    Next
End Sub

It does work, However it still loops thru all Blank Cell
Not the minimal amount of 15(Number of Blank Rows).
Which is what I'm looking for.
I know this seems nitpicky, but this was just a small Test Data Set,
only 4 Columns and 32 Rows
Actual Data Set for this case is 3000+ Rows and 76 Columns.
each missing record would cause 76 unneeded loops.

I came up with

Sub FindMissingRecords()
   Dim rIncompleteRecords As Range
   Dim i As Integer
 
CheckRecords:
   On Error Resume Next
   Set rIncompleteRecords = _
   Sheets("Fiscal Data").Range("C200:F232").SpecialCells(xlCellTypeBlanks)
   On Error GoTo 0
   If rIncompleteRecords Is Nothing Then 'No Missing Records Found
      Exit Sub
   Else
      GetSalesDate (Cells(rIncompleteRecords.Row, 1).Value)
      Sheets("Fiscal Data").Cells(rIncompleteRecords.Row, 3).Resize(1, 2).Value = "N"
MsgBox "Row Number: " & rIncompleteRecords.Row, vbOKOnly, "Row Number"
   End If
   Set rIncompleteRecords = Nothing
   GoTo CheckRecords
End Sub

Which Does provide the minimal amount of 16(15 Blank Rows and the last Check with No Missing Records). However it does not do it Top Down. This is required for this Workbook. Because if there is a legitimate reason for a missing record ie(Holiday). The previous records MTD info is copied down. and 0's are put into the Daily Fields.

It appears that SpecialCells(xlCellTypeBlanks) will not work for this purpose.

I decided to just go with .Find

Sub FindMissingRecords()
   Dim rIncompleteRecords As Range
 
CheckRecords:
   With Sheets("Fiscal Data").Range("C200:F232")
         Set rIncompleteRecords = .Find(What:="", _
         SearchDirection:=xlNext, _
         SearchOrder:=xlByRows)
   End With
   If rIncompleteRecords Is Nothing Then
      Exit Sub
   Else
      Sheets("Fiscal Data").Cells(rIncompleteRecords.Row, 3).Resize(1, 2).Value = "N"
   End If
   Set rIncompleteRecords = Nothing
   GoTo CheckRecords
End Sub

Again Thank You