Insert Delete Rows as per count.

Upload a small sample workbook with the code implemented and let us know what the code is not doing which it is supposed to do.

Hi,

on line 29 maybe better

    Ws2.Range("A30:A" & RowDiff + 2).Delete shift:=xlUp

Select allOpen in new window

than

    Ws2.Range("A30" & RowDiff + 2).Delete shift:=xlUp

Select allOpen in new window

Regards

Thanks Subodh & Rgonzo,

Rgonzo, I did changed but still no effect.

Subodh,

I have lots of sheets and many modules to run the file, but I am attaching only the sheets which needs correction.

Once user clicks print statement, UserForm Pops up, user will select for which customer he/she needs to print statement, then he/she needs to select the invoice format 811KY or 822AO or 511KY (I have to put this option because I am failing to extend the rows more that 60 rows), If I get rows insertion/deletion, then I may be able to remove this option.

Hope this info may help.

Thanks again for your help.
Aging-Analysis-Test.xlsm

If you have an issue with downloading and opening the file due to a temporary bug in the forum, first download and save it on your system and then open it.

Thanks a lot Subodh,

I will try with all the required changes I would like to make and revert to you,

For now initial requirement of insert/delete rows are perfectly working fine.

Subodh,

I am little bit confused, before I mess your code, I want to make sure, how I am make change to my below code which was in other module: Update_StatementTemplate():

This code changes the number format as per cell value, if I am gonna incorporate your code below range will fluctuate.

If Ws2.Range("I2") = "$" Then
    Ws2.Range("H87:H90").NumberFormat = "[$$ ]#,##0.00_ ;-[$$ ]#,##0.00 "
ElseIf Ws2.Range("I2") = "AOA" Then
    Ws2.Range("H87:H90").NumberFormat = "[$AOA ]#,##0.00_ ;-[$AOA ]#,##0.00 "
End If

Select allOpen in new window

Same for below, I am copying the range from start row of "A" column to "H" till the last row:

Ws2.Range("A25:H25").Copy
Ws2.Range("A25:H82").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Select allOpen in new window

Please help.
Regards,
Shums

Thanks Subodh,

That really makes sense. I did accordingly.

Now lastly, I also have below code in UserForm, which sends the details to cells like currency

If CurrencyComboBox.value = "$" Then
    Ws.Range("I2").value = "$"
    [b]Ws.Range("H76").value = ""[/b]
ElseIf CurrencyComboBox.value = "AOA" Then
    Ws.Range("I2").value = "AOA"
    [b]Ws.Range("H76").value = ExchangeRate.value[/b]
End If

Select allOpen in new window

How I am gonna change there?

Not sure which cell is being referred by H76 in your original workbook.
But you can try to find that cell dynamically in the code.

Let's assume you are referring to the cell H85 as per your original sample workbook. And you will find a header in F85 as "Total Devido/Total Due". So you can try the code something like below to find the string "Total Devido/Total Due" in column F dynamically so that you would know the location of H85 and replace Ws.Range("H76").value with HCell

Dim FCell As Range, HCell As Range
Set FCell = Ws.Range("F:G").Find(what:="Total Devido/Total Due", LookIn:=xlValues, lookat:=xlWhole)
If Not FCell Is Nothing Then
   Set HCell = FCell.Offset(0, 1)
   If CurrencyComboBox.value = "$" Then
       Ws.Range("I2").value = "$"
       HCell.value = ""
   ElseIf CurrencyComboBox.value = "AOA" Then
       Ws.Range("I2").value = "AOA"
       HCell.value = ExchangeRate.value
   End If
End If

Select allOpen in new window

I already messed it up.

I will come back to you soon.

Thanks a lot for your help.

Kind Regards,

Good Day Neeraj,

I dont want to spoil your Sunday. But you are very kind to help me with my project.

I changed the minrow to 60 to fit one page.

Everything working fine, except one thing if previous count selection was 60 and new count is also less than 60, then it deletes the last two rows which disturbs the border of my template. I think the problem is within this code:

Ws2.Range("A" & StartRow + minRow - 1 & ":A" & eRow - 1).EntireRow.Delete

Select allOpen in new window

I think eRow -1 deletes the last two rows.

Good Day Neeraj,

I am attaching revised workbook. Please check where what I am doing wrong.

At the end of the process I also have below code to auto fit the column, but seems its not reading.

Ws2.Columns("H:H").AutoFit

Select allOpen in new window

Aging-Analysis-Test.xlsm

Hi Neeraj,

I have made some correction for my above queries.

1. Row delete remains unchanged if its less than 60 rows (minRows)
2. For Column Autofit, I have assigned Dim LastCell As Long and changed the code to

LastCell = Ws2.Range("H" & Rows.Count).End(xlUp).row
Ws2.Range("H" & LastCell).Columns.AutoFit

Select allOpen in new window

Now I have two other problems:
1. I don't know where to place clear previous contents code:

Ws2.Range("A" & StartRow + minRow - 1 & ":A" & eRow - 1).ClearContents

Select allOpen in new window

2. If previous count was greater than minRows count (60rows), it doesn't delete correctly for new selection.

Please see revised attached sheet.
Aging-Analysis-Test.xlsm

Thanks Neeraj for your assistance in closing this project.

See you soon with other project.