Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Carlos Nunes-Ueno"
wrote in message
I work in an accounting firm and for audits we sometimes get general ledger
documents in Excel that we then process to see if there are interesting
patterns, etc.
The problem is that most of the time, these are basically puked into Excel
in print formats by whatever accounting application the client is using.
This means that instead of each transaction having it's own row with all of
the information, some of the information might be in a simulated "header" or
"footer" and there might be random columns inserted willy-nilly to replicate
how it looks on paper.
To address this, I usually hack out a little custom function that takes the
transactions from the raw data and puts them in neat rows in another
worksheet.  My issue is that these tend to be fairly slow and resource
intensive.  When they run, Excel immediately jumps to 100% CPU usage and
processing around 3500 rows takes five minutes or sometimes more.  I'm much
more comfortable with Access VBA than with Excel and therefore I get the
feeling that there is probably a lot of optimization that could done to the
code.  I'd appreciate any ideas that could speed up this code.
Anyway, without further ado, here's an example:

Public Sub CleanUp(intMaxRow As Integer, AcctNumCol As Integer, AcctNameCol
As Integer, ParamArray Cols() As Variant)
   'Walk down the RawData sheet and look for one of two patterns,
"Account*" or "??##"

   'If "Account*" is found, then copy the Account Name and Number to string
variables
   'these will be used later for copying into the CleanData sheet.

   'If "??##" this row is a transaction, so start building a discontinuous
range using the
   'columns specified in the Cols paramarray.

   Const StartRow As Integer = 2

   Dim wksRawData As Worksheet
   Dim wksCleanData As Worksheet
   Dim rngRawData As Range
   Dim rngTemp As Range

   Dim intRawRow As Integer
   Dim intCleanRow As Integer
   Dim strAcctNum As String
   Dim strAcctName As String
   Dim intColCnt As Integer

   Application.ScreenUpdating = False

   intCleanRow = StartRow

   Set wksRawData = Application.Worksheets("RawData")
   Set wksCleanData = Application.Worksheets("CleanData")

   'Walk through the RawData sheet
   For intRawRow = 1 To intMaxRow
       If wksRawData.Cells(intRawRow, 1) Like "Account*" Then
           strAcctNum = Trim(wksRawData.Cells(intRawRow, AcctNumCol))
           strAcctName = Trim(wksRawData.Cells(intRawRow, AcctNameCol))
       ElseIf wksRawData.Cells(intRawRow, 1) Like "??##" Then

           'This row is a transaction, write in the account number and name
           wksCleanData.Cells(intCleanRow, 1) = strAcctNum
           wksCleanData.Cells(intCleanRow, 2) = strAcctName

           'Initialize the beginning of the info range
           Set rngRawData = wksRawData.Cells(intRawRow, Cols(0))

           'Build the range with the columns we need
           For intColCnt = 1 To UBound(Cols)
               Set rngTemp = wksRawData.Cells(intRawRow, Cols(intColCnt))
               Set rngRawData = Application.Union(rngRawData, rngTemp)
           Next intColCnt

           Set rngTemp = wksCleanData.Cells(intCleanRow, 3)

           rngRawData.Copy rngTemp

           intCleanRow = intCleanRow + 1
       End If
   Next intRawRow

   'Release all objects
   Set rngTemp = Nothing
   Set rngRawData = Nothing
   Set wksCleanData = Nothing
   Set wksRawData = Nothing
   Application.ScreenUpdating = True
End Sub