View Full Version : Any 'gotchas' here?

02-26-2012, 12:55 PM
I am working with huge 2007/2010 tabs often containing 90-200,000 rows. I summarize specific columns by date into a db table. The dates, however, are in the format - example:112//2/19 which I convert to 2/19/2012 via a UDF.

The files are dumped daily just adding rows, consolidated monthly, the file basically overwritten. This leaves me with a parsing law of diminishing returns, as I must create a daily lookup table to ensure as I summarize the daily tabs I avoid unnecessary propogation of values, viz: if 2/19/2012 data is already summarized ignore rows with that date.

If you are confused at this point: assume 1-31 March 2012, with 5-10,000 rows added daily. I start parsing on March 2 for March 1 data - 5,000 rows. On March 3 the file contains March1+March2 so let's say it now has 12,000 rows. My parser would then read rows, eliminating all the previously summarized March1 data, then updating the db table with March2 data. So you can see why I used the term 'diminishing returns'.

So, now to the point. Assuming the data is concatenated daily, then the last rows contains the latest dates. So rather than performing a lookup moving from the beginning for n rows, I simply work backward from the last row until I have a successful lookup, then simply stop parsing.

With Excel 2007/2010 I have looked at


which appears to be what I want. My question boils down to: does anyone have knowledge or experience where this might return an incorrect value:dunno

Also: I'm open for suggestions for a better mousetrap.

02-26-2012, 01:11 PM
If you were to clear the last rows, LastCell will show the value of the last used row (now cleared)
This should be more reliable
Row = ows.Cells.Find("*", after:=Range("A1"), searchorder:=xlRows, searchdirection:=xlPrevious).Row

02-26-2012, 01:16 PM
BTW would this method help?

The xlCellTypeSameFormatConditions constant tells Excel that you want to
find all the cells with the same *condition format* as the currently
selected cell. It doesn't have anything to do with the formats that you
apply to the cell using the Format/Cells menu, for example.

The only way to do what you are trying to do is through brute force VBA
enumeration of all the cells in the used range of the active worksheet.
Here's one example of how it could be done:

Sub SelectBlueItalicFont()
Dim rngCell As Range
Dim rngAccumulate As Range
For Each rngCell In ActiveSheet.UsedRange
If rngCell.Font.Italic And (rngCell.Font.ColorIndex = 5) Then
If rngAccumulate Is Nothing Then
Set rngAccumulate = rngCell
Set rngAccumulate = Union(rngAccumulate, rngCell)
End If
End If
Next rngCell
If Not rngAccumulate Is Nothing Then rngAccumulate.Select
End Sub

Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals

02-26-2012, 02:18 PM

The data is dumped from a mainframe, and exists as a hidden tab, so I'm sure nothing is messed with prior to my accessing it.

For fun I will compare both methods for the lastrow and let you know about any failures.

As for the 2nd suggestion.... this is raw and somewhat clumsy data, the column heads are often things like "The % of jobs Completed"... and you can imagine what occurs trying to process the tab via ADO :banghead: ...

Brute force is going to be it until I get access to the backend and can do everything with SQL.