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].
To view this discussion on the web, visit
https://groups.google.com/d/msgid/smug/AM4P191MB0099BF8AC912B860D75AB919B1060%40AM4P191MB0099.EURP191.PROD.OUTLOOK.COM.
