PDA

View Full Version : Remove Multiple Header Rows



Foncesa
10-27-2013, 07:08 AM
Hi,
I Import multiple XML file, format of file is same, On every import it creates a header row for table, I need a macro which deletes the table header row and creates a single table and header.

Table 1 :

| Person | Week Of | Task | Hours|
| Bob | 1/6/13 | Foo | 12 |
| Mary | 1/6/13 | Foo | 7 |
| Mary | 1/6/13 | Bar | 5 |
| John | 1/6/13 | Foo | 5 |
| John | 1/13/13 | Foo | 13 |
Table 2 :
| Person | Week Of | Task | Hours |
| Bob | 1/6/13 | Baz | 3 |
| Mary | 1/6/13 | Baz | 2 |
| John | 1/13/13 | Baz | 5 |
Result:
| Person | Week Of | Task | Hours |
| Bob | 1/6/13 | Foo | 12 |
| Mary | 1/6/13 | Foo | 7 |
| Mary | 1/6/13 | Bar | 5 |
| John | 1/6/13 | Foo | 5 |
| John | 1/13/13 | Foo | 13 |
| Bob | 1/6/13 | Baz | 3 |
| Mary | 1/6/13 | Baz | 2 |
| John | 1/13/13 | Baz | 5 |

Paul_Hossler
10-27-2013, 07:39 AM
"Please" ?

"Thank You" ?

Other than that, can you attach sample of the XML file? Please

After you import the XML file into a worksheet, does column A contain the word 'Table'?

Paul

Foncesa
10-27-2013, 11:12 AM
After you import the XML file into a worksheet, does column A contain the word 'Table'?
Paul


No, Col A does not have word 'Table'.

Sample file uploaded.

Paul_Hossler
10-27-2013, 02:38 PM
Well the sample file does not match the fields in the first post

| Person | Week Of | Task | Hours|


It has


id
author
title
genre
price
publish_date




so you'll probably have to modify the example below:




Option Explicit
Sub DeleteRows()

Dim loTable As ListObject
Dim iRow As Long

Application.ScreenUpdating = False

With Worksheets("XML")

For Each loTable In .ListObjects
loTable.Unlist
Next


For iRow = .Cells(1, 1).CurrentRegion.Rows.Count To 2 Step -1
If .Cells(iRow, 1).Value = "id" Then .Rows(iRow).Delete
Next iRow
End With
Application.ScreenUpdating = True

End Sub



Paul

Foncesa
10-27-2013, 08:57 PM
Hi,

Paul thankyou for your help.

Paul_Hossler
10-28-2013, 01:28 PM
You're welcome

Glad to help

Paul