I have a spreadsheet (Table) with about 80,000 rows and 50 columns. One of the requirements is that if data is pulled from the database, any empty cells should have the word "Null" written in them, and if a Date is less than XXX, that, again, the word "Null" be placed within the cell. There are a couple of other items, but those two consume the most time within the formatting portion of the Data pull code.
Currently a For..If...Next
is being used for each. So the code has to cycle twice through all the cells. Since the ability to perform a write once, after pulling the data from the database is possible, is there a way to select a range from the Table, oTableName.DataBodyRange
, and perform a write once, or some other method which limits the amount of looping?
I did try the:
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "NULL"
.SpecialCells(xlCellTypeBlanks).HorizontalAlignment = xlCenter
But that only works for about 10 out of the several hundred blank cells that I can see, why still remains a mystery.
The If .Cells = "" or Date < XXX Then
, doesn't work either, as Dates have proven to be problematic. I need to check the format of cell, to see if has been formatted as a Date AND that the Date is < 01/01/2011. Trying to just use If Date is < 01/01/2011
doesn't work alone. Excel converts the date to a numerical string, so a cell value less than 39000 will be overwritten with Null. Therefore, the cell needs checked first to see if it has been formatted as a date, then the date number of 01/01/2011 is less than anything this year. So it needs a seperate IF..Then
Given all of this, is there way to reduce the formatting portion of the code?