Ytukyg

I'm trying to write a macro that import specific XML data into an existing XML map in an Excel file with predefined columns. Not all XML data is supposed to be imported into the sheet. I've used this approach below before without issue. However, I noticed that the macro took a awful long time to run and then I realized, that the table containing the XML data after the end of XML data inserted empty rows all the way to end row of the Excel sheet. When I import manually, this doesn't happen.

How do I ensure that the table holding the xml data isn't expanded to the end of the Excel sheet? Am I overlooking something?

My macro code:

Sub importXML()



'Define worksheets

Dim ws1 As Worksheet: Set ws1 = Sheets("XML_data")



Application.ScreenUpdating = False



'Finding last row in tabel

lastRow = ws1.Range("A1").Offset(ws1.Rows.Count - 1, 0).End(xlUp).Row



'Deleting old xml map data

If lastRow > 2 Then

    ws1.Range("A2:H" & lastRow).EntireRow.Delete

End If



'Define separators and apply.

Application.DecimalSeparator = "."

Application.ThousandsSeparator = ","

Application.UseSystemSeparators = False



fileToOpen = Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)



Application.DisplayAlerts = False

If IsArray(fileToOpen) Then

    For Each fil In fileToOpen

        ActiveWorkbook.XmlMaps("myXmlMap").Import URL:=fil

    Next fil

Else

    Exit Sub

End If

Application.DisplayAlerts = True



Application.UseSystemSeparators = True



End Sub