SpecialCells(xlCellTypeBlanks) Not to Delete Rows

C

Cincy Fan

Board Regular
Joined
Apr 1, 2009
Messages
77
(y)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:
Code: 
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Since the blanks are a discontiguous range of cells that may be all separate from each other, I don't think row count is meaningful. In any case, Excel is probably defaulting to give the row count of only the first cell in your special cells collection.

Here's a shot. I'm counting rows as I go through the cells found to "kill two birds with one stone". We just use a for-each loop to iterate the cells in the range object (for each cell in range).

There is a small gotcha here. If no cells are found Excel will crash (i.e., if there are no blanks). So we wrap that in a little error handler too.

Code: 
Sub FindMissingRecords()
Dim rIncompleteRecords As Range
Dim c As Range
Dim i As Integer
Dim x As Long
Dim intCount As Long
Dim blnCellsFound As Boolean

[COLOR="SeaGreen"]'//Inline error handling in case no cells found ...[/COLOR]
On Error Resume Next
Set rIncompleteRecords = Sheets("Sheet1").Range("C200:F232").SpecialCells(xlCellTypeBlanks)
If Not Err Then
    blnCellsFound = True
End If
On Error GoTo 0

If blnCellsFound Then
    For Each c In rIncompleteRecords.Cells
        [COLOR="seagreen"]'//Loop by cells and check for row numbers[/COLOR]
        If c.Row <> x Then
            x = c.Row
            intCount = intCount + 1
            MsgBox "Value in A" & x & ": " & Range("A" & x).Value
        End If
    Next c
    MsgBox intCount & " rows found."
End If

End Sub
 
Last edited:
Upvote 0
Thank You Xenou

We didn't Quite hit it this Time.

Here are the MsgBox Results

c.Row Values : Dates Correct What is in Test Data No blanks in E & F
Value in A200: Correct Date Col C & D Both Blank
Value in A201: Correct Date Col D Blank
Value in A203: Correct Date Col C & D Both Blank
Value in A202: Correct Date Col C Blank
Value in A203: Correct Date Col C & D Both Blank
Value in A204: Correct Date Col C Blank
Value in A205: Correct Date Col D Blank
Value in A206: Correct Date Col C & D Both Blank
Value in A207: Correct Date Col C & D Both Blank
Value in A208: Correct Date Col C & D Both Blank
Value in A209: Correct Date Col C & D Both Blank
Value in A210: Correct Date Col C & D Both Blank
Value in A211: Correct Date Col C & D Both Blank
Value in A206: Correct Date Col C & D Both Blank
Value in A207: Correct Date Col C & D Both Blank
Value in A208: Correct Date Col C & D Both Blank
Value in A209: Correct Date Col C & D Both Blank
Value in A210: Correct Date Col C & D Both Blank
Value in A211: Correct Date Col C & D Both Blank
Value in A212: Correct Date Col C Blank
Value in A231: Correct Date Col C & D Both Blank
Value in A232: Correct Date Col C & D Both Blank
22 Rows Found.

Side Question:
I'm familiar with Error Trapping but not no exactly what
On Error GoTo 0 does.
How does the differ from
On Error Exit Sub or Function?
 
Upvote 0
Is this really the value in A200 (?): Correct Date Col C & D Both Blank Anyway, that seems to suggest it's working - it found the blanks in c & d and gave the value from cell A in the row. Here's my test sample which works flawlessly: <a href="http://northernocean.net/etc/mrexcel/20100522_blanks.zip">Sample Workbook (zip file)</a>

My results (using my test data - the values in A are keyed to the row numbers):
Value in A203: 203
Value in A204: 204
Value in A206: 206
Value in A209: 209
Value in A213: 213
Value in A216: 216
Value in A219: 219
Value in A222: 222
Value in A225: 225
Value in A228: 228
10 rows found.

The same test data is reproduced below (it should be pasted into Cells A200 - then split out by spaces using text to columns). You may need to say more about what's not working. This is provided should you not feel comfortable with downloading the file. (But it has pushed all the blanks to the end of each row - no matter same difference).

200 x x x x x
201 x x x x x
202 x x x x x
203 x x x x
204 x x x x
205 x x x x x
206 x x x x
207 x x x x x
208 x x x x x
209 x x x x
210 x x x x x
211 x x x x x
212 x x x x x
213 x x x x
214 x x x x x
215 x x x x x
216 x x x x
217 x x x x x
218 x x x x x
219 x x x
220 x x x x x
221 x x x x x
222 x x x
223 x x x x x
224 x x x x x
225 x x x x
226 x x x x x
227 x x x x x
228 x x x x
229 x x x x x
230 x x x x x
231 x x x x x
232 x x x x x
 
Last edited:
Upvote 0
I was Unable to Download the Workbook :(

Here was my Test Data I was working with.
Correct Date meant on row 200 it did pull Apr 19


Starting in row 200
<TABLE style="WIDTH: 121pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=163 x:str><COLGROUP><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" span=4 width=19><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 35pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 width=47 x:num="40287">Apr 19</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 width=19>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40288" x:fmla="=A1+1">Apr 20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40289" x:fmla="=A2+1">Apr 21</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40290" x:fmla="=A3+1">Apr 22</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40291" x:fmla="=A4+1">Apr 23</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40292" x:fmla="=A5+1">Apr 24</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40293" x:fmla="=A6+1">Apr 25</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40294" x:fmla="=A7+1">Apr 26</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40295" x:fmla="=A8+1">Apr 27</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40296" x:fmla="=A9+1">Apr 28</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40297" x:fmla="=A10+1">Apr 29</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40298" x:fmla="=A11+1">Apr 30</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40299" x:fmla="=A12+1">May 01</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40300" x:fmla="=A13+1">May 02</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40301" x:fmla="=A14+1">May 03</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40302" x:fmla="=A15+1">May 04</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40303" x:fmla="=A16+1">May 05</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40304" x:fmla="=A17+1">May 06</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40305" x:fmla="=A18+1">May 07</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40306" x:fmla="=A19+1">May 08</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40307" x:fmla="=A20+1">May 09</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40308" x:fmla="=A21+1">May 10</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40309" x:fmla="=A22+1">May 11</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40310" x:fmla="=A23+1">May 12</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40311" x:fmla="=A24+1">May 13</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40312" x:fmla="=A25+1">May 14</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40313" x:fmla="=A26+1">May 15</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40314" x:fmla="=A27+1">May 16</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40315" x:fmla="=A28+1">May 17</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40316" x:fmla="=A29+1">May 18</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40317" x:fmla="=A30+1">May 19</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40318" x:fmla="=A31+1">May 20</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl27 height=17 x:num="40319" x:fmla="=A32+1">May 21</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl26 width=40>AAA</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl28 width=19></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25>X</TD></TR></TBODY></TABLE>

I'm thinking as you stated earlier these are non-contiguous Ranges
Therefore I believe that .Areas will be needed,
Would Union be helpful?

? rIncompleteRecords.Address
$C$200,$D$200:$D$201,$D$203,$C$202:$C$204,$D$205:$D$211,$C$206:$C$212,$C$231:$D$232

Thank You Again for Assisting.
 
Upvote 0
Ah, right. I see. It's jumping up and down depending on where the areas are, and so the rows don't go in order as needed for my test.

Well, you can use areas I'm sure but I found it tricky so I fell back on a vb dictionary which uses keys. By using the row numbers as keys I can test for their existence and then skip those already counted. But this isn't in order - so we could jump from 201 to 203 back to 202 -- the result is correct, just not sorted. Will that work? Do we need to tweak some more? I can show you some of my tries with areas but its a little embarassing ... they seem klunky to me ;) Live and learn, eh?

Code: 
Sub FindMissingRecords()
Dim rIncompleteRecords As Range
Dim c As Range
Dim i As Long
Dim x As Long
Dim intCount As Long
Dim blnCellsFound As Boolean
Dim msg As String
Dim d As Object

'//Inline error handling in case no cells found ...
On Error Resume Next
Set rIncompleteRecords = Sheets("Sheet1").Range("C200:F232").SpecialCells(xlCellTypeBlanks)
If Not Err Then
    blnCellsFound = True
End If
On Error GoTo 0


If blnCellsFound Then
    Set d = CreateObject("Scripting.Dictionary")
    For Each c In rIncompleteRecords.Cells
        '//Add cells to dictionary if row number is new
        If Not d.Exists(CStr(c.Row)) Then
            d.Add CStr(c.Row), c.Row
            x = c.Row
            intCount = intCount + 1
            Debug.Print "Value in A" & x & ": " & Range("A" & x).Value
        End If
    Next c
    Set d = Nothing
    msg = msg & vbCrLf & intCount & " rows found."
End If

End Sub
 
Last edited:
Upvote 0
I did like your solution, as always this is a nice learning location.
and I will take a deeper look into your solution.

Actually, I would prefer top down replacement of records.

I do not have in-depth knowledge of object programming.
But here's a thought, I would think this is possible.
can we Set rIncompleteRecords replace the first record.
then reSet rIncompleteRecords replace the first record.
Loop until Error, *** No Missing Records are Found ***
This should minimizes the number of loops required yet insuring completed records.

Again Thank You.
P.S. Two Hours past bed time will check back in the morning
:coffee:
 
Upvote 0
Yes, that would work fine too. It occurs to me that with only about 150 cells in total we don't need to really use special cells. It will be blazingly fast to loop through all the cells in order and find the blanks one by one. Another variation on the theme (now I can use my initial test to check for new rows, as we go in order in this case):
Code: 
Sub FindMissingRecords2()
Dim r As Range
Dim c As Range
Dim intCount As Long, x As Long

Set r = Sheets("Sheet1").Range("C200:F232")


    For Each c In r
        [COLOR="SeaGreen"]'//Loop by cells and check for row numbers[/COLOR]
        If Len(c.Value) = 0 Then [COLOR="SeaGreen"]'//a blank cell is found[/COLOR]
            If x <> c.Row Then
                x = c.Row [COLOR="SeaGreen"]'//a new blank row is found[/COLOR]
                intCount = intCount + 1
                Msgbox "Value in A" & x & ": " & Range("A" & x).Value [COLOR="SeaGreen"]'//report value in column "A"[/COLOR]
            End If
        End If
    Next c
    MsgBox intCount & " rows found."


End Sub
 
Upvote 0
This would work for this situation as the Test Data is a small Set.

What I'm looking for is a Data Integrity function that would work on any size Data Set.
The Current Data I'm using has 3000+ Rows and 76 Columns,
and I'm trying to Minimize the loop size to just what would be required.

I try to avoid loops as if their the plague I know their useful but when I use them I try to minimize the number of loops possible.
In the Test Data case 15 loops (Number of rows missing Data) as appose to 28 (Total Rows) or 112 (Total Cells).

I ran into a situation before processing downloaded Bank info where Loops were causing the macro to take 10 minutes to run,
by optimizing the loops I've brought the run time under 3 minutes.

I wondering now if I should just Call the Function get the first record missing Data,
return the Row to the calling procedure then loop there?

I do feel thought that we should loop until Error, Checking records with xlCellTypeBlanks.
Is This Possible?

Thank You Again
I do appreciate your assistances.
 
Upvote 0
I would go for:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Delete rows based on blank cell; then copy formula to an entire column
2
Replies
12
Views
826
Skyybot
Skyybot
cmschmitz24
  • Solved
Delete blank rows or skip to next if none
Replies
6
Views
324
Joe4
Joe4
Y
  • Question
Unwanted empty rows in csv file
2
Replies
11
Views
827
Alex Blakenburg
A
R
  • Question
Filter & Delete Deleting Header Row Suddenly
Replies
0
Views
217
reberryjr
R
S
  • Solved
Tweak Macro that Imports specific table columns from a different workbook to filter a column first
Replies
4
Views
159
sspatriots
S

Forum statistics

Threads
1,214,649
Messages
6,120,732
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top