java
c
python
linux
xcode
regex
mysql
eclipse
json
perl
algorithm
facebook
oracle
delphi
apache
mvc
php5
asp
api
jsp
To do this efficiently you should generate a 2-dimensional data with the data you want to write, then write it all in one go.
Something like the following. I prefer 0-based arrays for compatibility with other languages whereas you seem to be using a 1-based array (1 to UBound(filenames). So there may be off-by-one errors in the following untested code:
1 to UBound(filenames)
f = 1 maxcol = 'func call to get last non blank col ref .ie could return T, R, H.etc ' 2D array to hold results ' 0-based indexing: UBound(filenames) rows and maxcol columns Dim aValues(0 to UBound(filenames)-1, 0 To maxcol-1) As Variant Dim rngStart As Range Dim nColIndex As Long For f = 1 To UBound(filenames) Set aDoc = LoadXmlDoc(filenames(f)) aValues(f-1, 0) = filenames(f) For nColIndex = 1 To maxCol-1 aValues(f-1, nColIndex) = aNode.Text Next nColIndex Next f ' Copy the 2D array in one go Worksheets("Results").Offset(1,0).Resize(UBound(filenames),maxCol).Value = aValues
As you're getting you results from XML, have you looked into using XML Maps to display the information - might not be suitable for your situation, but worth a try.
This link below shows some stuff about using XML maps in Excel.
The syntax of the line to load an XML string into a define map is similar to this:
ActiveWorkbook.XmlMaps("MyMap").ImportXml(MyXMLDoc,True)
You might want to look at my code in "Using Variant Arrays in Excel VBA for Large Scale Data Manipulation", http://www.experts-exchange.com/A_2684.html (further detail provided in the hyperlink)
Note that as I don't have your data above to work with the article provides a sample solution (in this case efficiently deleting leading zeroes) to meet you filling a range from a 2d array requirement.
Key points to note
Here is the code:
'Press Alt + F11 to open the Visual Basic Editor (VBE) 'From the Menu, choose Insert-Module. 'Paste the code into the right-hand code window. 'Press Alt + F11 to close the VBE 'In Xl2003 Goto Tools … Macro … Macros and double-click KillLeadingZeros Sub KillLeadingZeros() Dim rng1 As Range Dim rngArea As Range Dim lngRow As Long Dim lngCol As Long Dim lngCalc As Long Dim objReg As Object Dim X() On Error Resume Next Set rng1 = Application.InputBox("Select range for the replacement of leading zeros", "User select", Selection.Address, , , , , 8) If rng1 Is Nothing Then Exit Sub On Error GoTo 0 'See Patrick Matthews excellent article on using Regular Expressions with VBA Set objReg = CreateObject("vbscript.regexp") objReg.Pattern = "^0+" 'Speed up the code by turning off screenupdating and setting calculation to manual 'Disable any code events that may occur when writing to cells With Application lngCalc = .Calculation .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With 'Test each area in the user selected range 'Non contiguous range areas are common when using SpecialCells to define specific cell types to work on For Each rngArea In rng1.Areas 'The most common outcome is used for the True outcome to optimise code speed If rngArea.Cells.Count > 1 Then 'If there is more than once cell then set the variant array to the dimensions of the range area 'Using Value2 provides a useful speed improvement over Value. On my testing it was 2% on blank cells, up to 10% on non-blanks X = rngArea.Value2 For lngRow = 1 To rngArea.Rows.Count For lngCol = 1 To rngArea.Columns.Count 'replace the leading zeroes X(lngRow, lngCol) = objReg.Replace(X(lngRow, lngCol), vbNullString) Next lngCol Next lngRow 'Dump the updated array sans leading zeroes back over the initial range rngArea.Value2 = X Else 'caters for a single cell range area. No variant array required rngArea.Value = objReg.Replace(rngArea.Value, vbNullString) End If Next rngArea 'cleanup the Application settings With Application .ScreenUpdating = True .Calculation = lngCalc .EnableEvents = True End With Set objReg = Nothing End Sub