Add New Row & Delete Blank Rows in Worksheet

[Priyantha]

Dear All,

I tried to create a macro for saving a workbook & add a new row in a table. there are a few issues in the macros I created as below.

I tried to fill the table in sheet "Loan Schedule" using sheet "Loan Format" (Distress Loan format.xlsm). If there are blank rows to write data in the table, I want to add a new row below (Between B9 & B10) with the same format as the above row and should automatically update the total row (D10, E10, etc...)

Next, I try to send data to the other file (Cust out Format - Distress Loan .xls) while sitting on the "Loan Schedule" sheet (Distress Loan format.xlsm). Then, data is written only when the file (Cust out Format - Distress Loan .xls) is open, and data is written beyond the expected number of rows in some columns (I want to 3 rows but there are data in 7 rows in Colum C).

Please help me to correct this issue.

Thanks,

Priyantha

[HansV]

In the macro Creating_CUST_File, change the line

For j = 1 To lr

to

For j = 1 To lr - 4

[HansV]

in the macro Distress_Loan, change the lines

Code: Select all

If ws2.Cells((lr + 1), 2).Value <> "" Then
   ws2.Cells(lr, 2).EntireRow.Insert Shift:=xlDown
   ''ws2.Rows.Insert Shift:=xlDown
End If

to

Code: Select all

If ws2.Cells((lr + 1), 1).Value <> "" Then
   ws2.Cells(lr, 1).EntireRow.Copy
   ws2.Cells(lr + 1, 1).EntireRow.Insert
   Application.CutCopyMode = False
End If

[Priyantha]

Dear Hans,

Thanks for your guidance. Is there any solution to automaticaly update "Total" row ?

Thanks,

Priyantha

[HansV]

Assuming that the totals are currently in row 10, change the formula in D10 to

=SUM(D$4:OFFSET(D10,-1,0))

Then fill to the right to I10. The formula will update itself when a new row is inserted. No code needed.

[Priyantha]

Dear Hans,

It is working, Thanks,

Priyantha