asked on

Marco to move data and remove balnk rows

I have an excel spreadsheet which is the result of exporting data from a crystal report
The crystal report has data from a sub report and apparently the following exists
"The export to Excel is designed to export the subreport to the "next" row. There is nothing you can do about that."

So is it possible to have a macro that puts the data under the correct headings and removes the blank rows
Have attached the excel file
Example the figures in row 3 should be in row 2 under the headings:- Reg Hrs Qty Reg Hrs Cost O/T Hrs Qty O/T Factor OT Hrs Cost Total Hrs Cost

Noting that some records do not contain data to move (example row 65)

Gordon
Test-Chargeable.xlsx

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

Try this, it puts the result on a new sheet.

Sub TransformChargeable()
Dim arrIn As Variant
Dim arrOut()
Dim I As Long
Dim J As Long
Dim cnt As Long
    
    With Sheets("Sheet1")
     arrIn = .Range("A2", .Range("A" & Rows.Count).End(xlUp).Offset(, 11)).Value
    End With
    
    I = 1
    Do
        cnt = cnt + 1
        ReDim Preserve arrOut(1 To 18, 1 To cnt)
        
        For J = 1 To 12
            arrOut(J, cnt) = arrIn(I, J)
        Next J
        
        If arrIn(I + 1, 1) <> "" Then
            For J = 1 To 6
                arrOut(J + 12, cnt) = arrIn(I + 1, J)
            Next J
            I = I + 3
        Else
            I = I + 2
        End If
        
    Loop Until I > UBound(arrIn, 1)
    
    Sheets.Add
    
    ActiveSheet.Rows(1).Value = Sheets("Sheet1").Rows(1).Value
    
    ActiveSheet.Range("A2").Resize(UBound(arrOut, 2), UBound(arrOut, 1)).Value = Application.Transpose(arrOut)

End Sub

Open in new window

Gordon Hughes

ASKER

Hi Rgonzo1971

The macro seems to work well
How do I save it so that the macro can be seen and used where required on any excel spreadsheet
Gordon

Rgonzo1971

for that you could refer to
https://support.office.com/en-us/article/create-and-save-all-your-macros-in-a-single-workbook-66c97ab3-11c2-44db-b021-ae005a9bc790

Gordon Hughes

ASKER

In principle I like the idea of saving the data in the format required in a new spreadsheet. the solution from Norie converts the date format from English to American format
But still don't know how to save the macro so that is accessible from any excel spreadsheet
Gordon

Rgonzo1971

by creating a PERSONAL.xlsb file you can put the macros to be used in all the worksheets

the PERSONAL.xlsb is opened whenever excel is opening

so create a dummy macro like in the link then put the my code in the created module

Norie

Gordon

I didn't notice that about the date/times, I'll try and fix it and post back.

Gordon Hughes

ASKER

Ok am getting there can we fix the date format on the second solution?
Gordon

Gordon Hughes

ASKER

Thanks Norie

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.