macro for variable no. of rows

[rakeshshethia]

HI.


I am new to macros. Let me explain what I want the macros to do. The range is from column A to column O. No. of Rows are variable for different sheets downloaded from the system. As in, if the last row is at no. 30, all the columns would have data in last row at no. 30, but it is not necessarily 30 in all the excel files.


Part 1
I want to delete column B. Out of remaining columns, delete columns H to M. Now, the data in column G and H is in numbers. Column G is "Amount" and Column H is "Commission". I want the sum at the end of the respective columns.


Part 2
Later, I want the difference between the sums of Column G and Column H after 4 rows from the last row and in Column B. The adjacent cell in column A should have the text "Debit".

Part 2A
This is if I want the sum of Column G and H, however the number at fourth row from the last column is equal to the sum of G (and not the diff. as in Part 2)


I need macros for three different parts as it can be Part 1 AND either Part 2 or Part 2A in the sheet.


Please help.

 

[pbornemeier]

Option Explicit

Sub Part1()
    Dim lLastRow As Long
    Dim lLastColumn As Long
    
    With ActiveSheet
        lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Columns("B:B").Delete Shift:=xlToLeft
        .Columns("H:M").Delete Shift:=xlToLeft
        With .Range(.Cells(lLastRow, "F"), .Cells(lLastRow, "G"))
            .FormulaR1C1 = "=SUM(R2C:R[-1]C)" 'Assumes Data starts at Row 2
            .Value = .Value
        End With
    End With
End Sub


Sub Part2()
    Dim lLastRow As Long
    
    With ActiveSheet
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(lLastRow + 4, 1).Value = "Debit"
        With .Cells(lLastRow + 4, 2)
            .FormulaR1C1 = "=R[-4]C[5]-R[-4]C[6]"
            .Value = .Value
        End With
    End With
End Sub

Sub Part2A()
    Dim lLastRow As Long
    
    With ActiveSheet
        lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(lLastRow + 4, 1).Value = "SUM G:H"
        With .Cells(lLastRow + 4, 2)
            .FormulaR1C1 = "=R[-4]C[5]+R[-4]C[6]"
            .Value = .Value
        End With
    End With
End Sub

 

[rakeshshethia]

Hi,

Thanks for your reply.

What I did was.. I downloaded a new dump from the system. (An excel file named Personal opens along with this dump file. The Personal file is something I had created while trying to create macros. I am not sure why it opens up everytime I open an Excel file. But it used to have some macros which I tried to create and later deleted).
What I did was -
Hit Alt+F11>Insert>Module> copy pasted part 1, again inserted new module, copy-pasted part 2 and similarly part2A.

Then I opened the downloaded sheet, selected Developer>Macros>Part1 > Run.

The result >
1) It shows the sum at the third row from the last row
2) The sum shown is for columns F and G

Expected..
1) The sum should be shown at the very next row from the last row
2) The sum should be for the column G and H.

I then ran Part 2. It shows the word "Debit" four rows (correctly) after the row that has the sum of column G.
However, the number next to Debit is not the difference between sums of Columns G and H. Its just the sum of Column G (data in Column F is text data, so the sum shown is 0).

I now downloaded a new sheet, ran Part 1, results same as above, and then ran Part 2A. Result..
Instead of word "Debit" at the fourth row from the row that has the sum of column G, it says "SUM G:H" and the amount is the sum of Column G. Again, for Part 2A, it should show the word "Debit" at fourth row from the last row, and just the sum of Column G next to it.

Thanks for your help..

 

[pbornemeier]

Hide the personal.xlsb file by bringing it to the foreground and clicking View | Hide in the ribbon. The macros in it will still be available to any other open workbook, but personal.xlsb will not be visible.

I misunderstood your reference to columns G & H in the OP. I though you meant the original G & H (which become F & G after Part 1).

If there are blank rows between the sum values below columns F & G and the last row of the data, it is due to non-printing characters in column A in cells below the visible data. The last row of the import data is determined with the formula .Cells(.Rows.Count, 1).End(xlUp).Row which finds the last cell in column A that contains any data. I added code to remove cells containing only space characters from the bottom of the imported range in column A. If there are any other invisible characters in those cells (CR, LF, Tab, etc) you will have to let me know what they are and some additional code will be required)

In the OP, Part 2A was supposed to show the sum of column G and column H, in Post#3 just the sum of column G...which is what it will currently produce.

Let me know if I have misunderstood your intent for any of these routines.

Option Explicit

Sub Part1()
    Dim lLastColARow As Long
    Dim lLastColumn As Long
    
    With ActiveSheet
        lLastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        lLastColARow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Columns("B:B").Delete Shift:=xlToLeft
        .Columns("H:M").Delete Shift:=xlToLeft
        
        'Remove blank cells from the bottom of column A
        Do While Trim(.Cells(lLastColARow, 1).Value) = vbNullString
            .Cells(lLastColARow, 1).Value = vbNullString
            lLastColARow = lLastColARow - 1
        Loop
        
        With .Range(.Cells(lLastColARow + 1, "G"), .Cells(lLastColARow + 1, "H")) 'Original columns were H & O
            .FormulaR1C1 = "=SUM(R2C:R[-1]C)" 'Assumes Data starts at Row 2
            .Value = .Value
        End With
    End With
End Sub


Sub Part2()
    Dim lLastColARow As Long
    
    With ActiveSheet
        lLastColARow = .Cells(.Rows.Count, 1).End(xlUp).Row
        'If 2 or 2A has already been run, reset lLastColARow to the last data row
        If .Cells(lLastColARow, 1).Value = "Debit" Then lLastColARow = lLastColARow - 4
        .Cells(lLastColARow + 4, 1).Value = "Debit"
        With .Cells(lLastColARow + 4, 2)
            .FormulaR1C1 = "=R[-3]C[5]-R[-3]C[6]" 'Column G Total - Column H Total
            .Value = .Value
        End With
    End With
End Sub

Sub Part2A()
    Dim lLastColARow As Long
    
    With ActiveSheet
        lLastColARow = .Cells(.Rows.Count, 1).End(xlUp).Row
        'If 2 or 2A has already been run, reset lLastColARow to the last data row
        If .Cells(lLastColARow, 1).Value = "Debit" Then lLastColARow = lLastColARow - 4
        
        .Cells(lLastColARow + 4, 1).Value = "Debit"
        With .Cells(lLastColARow + 4, 2)
            .FormulaR1C1 = "=R[-3]C[5]"     'Column G Total
            .Value = .Value
        End With
    End With
End Sub

 

[rakeshshethia]

This is great. thank you so much.

And yes.. there is text which displays "xx no. of records found".. (i.e. no. of rows) in column A at third line from the last row. The two rows in between do not have any data.

One more thing.. the no. that is displayed in column B beside "Debit" should be left aligned.

Thanks a lot once again.

 

[pbornemeier]

After this row in part2 & 2A:
With .Cells(lLastColARow + 4, 2)
add this line:
.HorizontalAlignment = xlLeft

 

[rakeshshethia]

Done. What do I do to get the sum of columns right below the last row.


Thank you.

 

[pbornemeier]

In all parts, change:
LastColARow = .Cells(.Rows.Count, 1).End(xlUp).Row
to
lLastColARow = .Range("A1").CurrentRegion.Rows.Count

 

[rakeshshethia]

Hi..
I did replace the text in all three parts. Part 1 works fine. The sum is shown right below the last row. However, in part 2 it shows the Debit figure as 0 and in part 2A, it gives the Run-time error 1004 "Application defined or object defined error".

I appreciate your reply. Please bear with my delayed replies.

 

[rakeshshethia]

Finally I got the PC I used to work on... I cant thank you enough for the gift. You saved so much of my time which no one else can ever give me.

Thank you.