Problem solved change the 4 to 3 in the second row of “Range(Cells” and you get five columns, change the 4 to 5 and you get six columns.
I know you’ll all be itching to know this since you ignored it in droves hehe Michael S Sent from my iPad Air 3 On 1 Feb 2020, at 12:36, Michael Simpson <[email protected]> wrote: Still need a reply in making the macro work on a list without blanks. Corrected macro. Sub Move_Rows() ' ' Move_Rows Macro ' Moves cemetery row data for each grave into separate columns ' Need to remove spaces as my list has no spaces ' Application.ScreenUpdating = False i = 21 'Initial row number Do While Cells(i, "A") <> "" Range(Cells(i, "A"), Cells(i + 4, "A")).Select Selection.Copy Cells(i - 1, "A").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, skipBlanks:= _ False, Transpose:=True Range(Cells(i, "A"), Cells(i + 4, "A")).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp i = i + 1 Loop Application.ScreenUpdating = True End Sub ——————— List with Blanks Date Time Number Type Cost 28 Dec 22:57 07718 224128 SMS £0.00 28 Dec 22:57 07728 224218 SMS £0.00 29 Dec 11:02 07638 224458 SMS £0.00 29 Dec 20:52 07931 348694 SMS £0.00 ———————— List with no Blanks Date Time Number Type Cost 28 Dec 22:58 07718 224128 SMS £0.00 28 Dec 22:57 07728 224218 SMS £0.00 29 Dec 11:02 07638 224458 SMS £0.00 29 Dec 20:52 07931 348694 SMS £0.00 —————— Showing 1 - 104 of 104 entries with a total cost of £0.00 Should look like this Date Time Number Type Cost 28 Dec 22:58 07718 224128 SMS £0.00 28 Dec 22:57 07728 224218 SMS £0.00 29Dec 11:02 07638 224458 SMS £0.00 29 Dec 20:52 07931 348694 SMS £0.00 —————— On 26 Jan 2020, at 1:28 am, Michael Simpson <[email protected]> wrote: Hi Folks, I have a sorting problem in my spreadsheet caused by BT when sorting their Bill in Excel. BT have stopped suppling the ability to download their bill in CSV format making it impossible to copy the bill into a spreadsheet. Any attempt to copy and paste the itemised calls from the website or the PDF file into Excel or Numbers results in either everything landing in one column, or all the rows collated in a single column. My attempts to produce a macro to sort it failed, due to me forgetting how to do it. I found a solution but it was old and there were no replies to enquiries for modifications to it. The macro works perfectly provided there is a space after 5 entries. I don’t have a space so I’m looking to have the macro adjusted to work without the space. i’ve reduced the list to 4 columns to save space in this message, Many thanks in anticipation. Bill with spaces My bill <unknown.png> <unknown.tiff> The `Macro ———————————————————————————————— Sub Move_Rows() ' ' Move_Rows Macro ' Moves cemetery row data for each grave into separate columns ' Need to remove spaces as my list has no spaces ' Application.ScreenUpdating = False i = 21 'Initial row number Do While Cells(i, "A") <> "" Range(Cells(i, "A"), Cells(i + 4, "A")).Select Selection.Copy Cells(i - 1, "A").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True Range(Cells(i, "A"), Cells(i + 4, "A")).Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp i = i + 1 Loop Application.ScreenUpdating = True End Sub ————————————————————————————————— The original https://stackoverflow.com/questions/33659029/move-rows-of-data-to-columns-then-delete-the-rows Many thanks Michael S -- You received this message because you are subscribed to the Google Groups "Sussex Mac User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]<mailto:[email protected]>. To view this discussion on the web, visit https://groups.google.com/d/msgid/smug/AM4P191MB0099BF8AC912B860D75AB919B1060%40AM4P191MB0099.EURP191.PROD.OUTLOOK.COM<https://groups.google.com/d/msgid/smug/AM4P191MB0099BF8AC912B860D75AB919B1060%40AM4P191MB0099.EURP191.PROD.OUTLOOK.COM?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "Sussex Mac User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web, visit https://groups.google.com/d/msgid/smug/AM4P191MB0099B1BD7470D46F2B5F4808B1180%40AM4P191MB0099.EURP191.PROD.OUTLOOK.COM.
