Copy every 2nd row to the end of the previous row, then delete every second row

Hi,
I need some help creating a VBA for the attached excel, that I download from my local electricity company.
The excel shows starting with row 9 my electricity meter readings in columns E - G, with some totals in columns H - J.
Row 10 is the meter reading of the electricity I put back into the grid. The columns are the same.
I would like to copy all values from row 10 (12, 14, 16...) columns D - J to the end of the row 9 (11, 13, 15...), then delete row 10 (12, 14, 16...). The resulting excel should only have rows where column D is "Energia consumida".

After the excel is updated I can copy all values into another excel I use to calculate my electricity cost per day, month, year.
FYI, I am using Mac Excel 2021.
Thanks for your help.
Gerd

Maybe something like this?

Please Login or Register  to view this content.

BSB

Well, that was fast, and it sure works. Thank you very much.
But I now realize, that I should have rearranged my columns first, before doing the copying.
Could you please first move columns G and I and insert it after column E.
Then we would do the copy.
This would have the meter readings next to each other and the totals next to each other.
I apologize for not putting this into my initial request.

Give this a try.

Please Login or Register  to view this content.

BSB

Thanks again BSB, this works great.
And I found what the UnMerge does. I had not paid attention to that columns heading being merged cells.

After I ran the VBA I see a difference in the cell formatting.
The original downloaded excel shows the numbers in columns E - J left adjusted in their cells, but after the copying was done the values in new columns K - Q are right adjusted in their cells. So the original values look like text, the copied values look like numbers.

And after the copy is done my original values now have a small green triangle on the top left of each cell, showing the value having an error. I can select the cells and do a convert to number, but thought I add a new line of code and have VBA to do this formatting.

I found some code on the forum to change the text values to numbers, added it to the VBA but it does not do anything. Obviously this new line is not correct. What code do I need to use to convert the original value to a number (I need numbers in my final calculation excel).

Please Login or Register  to view this content.

Gerd

Try this.

Please Login or Register  to view this content.

BSB

Thanks again for your help, BSB, it works just fine.
I was working on my main excel several hours yesterday.
But one thing I still needed to do with this VBA, is to sort the data by date ascending. The problem is, that the date format is Portuguese (dd/mm/yyyy).
I found some logic on the internet, that works on the date, but it only sorted column A and not all columns A - Q.
I tried various combinations of setting the range for the sort, but whenever it on the .Apply statement of the logic it triggers an error.
I was googling some more but have not found a solution yet.
I am hoping you could look at the code and tell me where the logic is wrong.
Thanks again for your help.
Gerd

Please Login or Register  to view this content.

You need to include the columns you wish to move with the sort range to make them move
only a slight change, add or amend the red in your code

Please Login or Register  to view this content.

Thanks for your help, davsth.
I actually had tried this but only with rng (without 1), but all my values changed to dates, but with rng1 it works correctly.

if you formatted the dates first so moved this line above the sort
rng.NumberFormat = "dd.mm.yyyy;@"
then redefined the rng as .Range("A9:Q" & lr) it would work the way you tried