Macro for formatting existing sheet

**TZ Saic** · 07-13-2015, 07:20 AM

Dear all,

Every month I get workbook formatted on certain way (same way, maybe few rows more or less). However, I need to reformat it and add some additional calculations. Please find attached file with input sheet and output sheet.

I would like to aviod manual adjustments in input file, but recording a macro did not work well either.

Is there a code that would do the trick?

Thank you,

TanjaFormatting.xlsx

**Rioran** · 07-13-2015, 08:09 AM

Hello, Tanja!

How about fast solution? If you will press button in the attachment - you'll see deletion of unneeded rows. Works well, if your data every time have the same amount of columns and starts from 5-th row. Part with formulas behind "AK" column is not done yet, but if this button is appropriate for you - it will be easy to add.

Sub Rio_Clean()
    Dim RowX As Integer, RowY As Integer, i As Integer
    With ThisWorkbook.ActiveSheet
        RowX = .Cells(.Cells.Rows.Count, 37).End(xlUp).Row
        RowY = .UsedRange.Row + .UsedRange.Rows.Count - 1
        If RowX < 5 Then Exit Sub
        If RowX < RowY Then .Rows((RowX + 1) & ":" & RowY).Delete
        For i = RowX To 5 Step -1
            Select Case .Cells(i, 37).Value
                Case "", "Check"
                    .Rows(i).Delete
                Case Else
                    If .Cells(i, 1).Value = "" Then
                        .Rows(i).Delete
                    End If
            End Select
        Next i
    End With
End Sub