Re: Excel Macro Problem error in macro corrected.

[Michael Simpson]

 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 <mikesim...@hotmail.com> 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

[Michael Simpson]

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 <mikesim...@hotmail.com> wrote:



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