Solved

Insert Delete Rows as per count.

Posted on 2016-08-11
16
63 Views
Last Modified: 2016-08-16
Dear Experts,

I want to count string of cell value of active sheet in another sheet and count non-blank rows of active sheet, find the difference between both these count and insert/delete rows.

I need your help in correcting below code.
Sub Insert_Delete_Rows()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim LR1 As Long, LR2 As Long
Dim MyCount1 As Integer, MyCount2 As Integer, RowDiff As Integer
Dim MyRange As Range

Application.ScreenUpdating = False

Set Ws1 = Worksheets("Aging_Report")
Set Ws2 = Worksheets("Statement_Template")
LR1 = Ws1.Range("C" & Rows.Count).End(xlUp).row
LR2 = Ws2.Range("C" & Rows.Count).End(xlUp).row
Set MyRange = Ws2.Range("C24:C" & LR2)

MyCount1 = Application.WorksheetFunction.CountIfs(Ws1.Range("D3:D" & LR1), Ws2.Range("A12").value, Ws1.Range("B3:B" & LR1), Ws2.Range("J1").value)
Ws2.Range("N1").value = MyCount1
MyCount2 = Application.WorksheetFunction.CountA(MyRange)
Ws2.Range("O1").value = MyCount2

If MyCount1 = MyCount2 Then
    Exit Sub
ElseIf MyCount1 > MyCount2 Then
    RowDiff = MyCount1 - MyCount2
    Ws2.Range("A25:H25").EntireRow.Copy
    Ws2.Range("A30:A" & RowDiff + 2).EntireRow.Insert
    Application.CutCopyMode = False
ElseIf MyCount1 < MyCount2 Then
    RowDiff = MyCount2 - MyCount1
    Ws2.Range("A30" & RowDiff + 2).Delete shift:=xlUp
End If

Application.ScreenUpdating = True
End Sub

Open in new window

0
Comment
Question by:Shums
  • 9
  • 6
16 Comments
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41753372
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.
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41753390
Hi,

on line 29 maybe better
    Ws2.Range("A30:A" & RowDiff + 2).Delete shift:=xlUp

Open in new window

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

Open in new window

Regards
0
 
LVL 1

Author Comment

by:Shums
ID: 41753466
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
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41753571
I have tweaked your macro along with the variable as I wrote it from the scratch.

On the Module8, the first line defines the minimum no. of Rows you want to appear on the Statement_Template Sheet. Right now it is defined to 5 and the template is adjusted accordingly to show 5 blank rows for data input.
You may change the variable value in the first line of code as per your requirement.

So as per the Customer Name in A12 on Template sheet will insert or delete the rows to accommodate the data form that customer.

Const minRow As Integer = 5

Sub Print_Statement()
PrintStatement.Show
End Sub

Sub Insert_Delete_Rows()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, sRow As Long, eRow As Long, cnt As Long, r As Long, n As Long
Dim eCell As Range, cell As Range

Application.ScreenUpdating = False

Set sws = Sheets("Aging_Report")
Set dws = Sheets("Statement_Template")
slr = sws.Cells(Rows.Count, "D").End(xlUp).row
sRow = 24
cnt = Application.WorksheetFunction.CountIf(sws.Range("D3:D" & slr), dws.Range("A12").value)
Set eCell = dws.Range("F:G").Find(what:="Total", LookIn:=xlValues, lookat:=xlPart)
If Not eCell Is Nothing Then
   eRow = eCell.row - 2
   r = eRow - sRow + 1
   If r < cnt Then
      n = cnt - r
      dws.Range("A" & eRow).Resize(n).EntireRow.Insert
   ElseIf r > cnt Then
      Range("A" & sRow + minRow - 1 & ":A" & eRow - 1).EntireRow.Delete
   End If
End If
Application.ScreenUpdating = True
End Sub

Open in new window

Aging-Analysis-Test.xlsm
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41753573
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.
0
 
LVL 1

Author Comment

by:Shums
ID: 41753666
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.
0
 
LVL 1

Author Comment

by:Shums
ID: 41754927
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

Open 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

Open in new window


Please help.
Regards,
Shums
0
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41754933
Hi Shums,

For the first code, instead of setting the number format with the code, set the number format with the help of conditional formatting by making new rules using the formulas ="$I$2="$" and ="$I$2="AOA" and set the number format accordingly. In this case if the rows are inserted or deleted, the conditional formatting will remain in those cells.

For the second code, whatever format you are trying to apply, apply that formatting to minRows i.e. 5 rows as per the code in advance so if you insert the rows, that formatting will automatically be copied into the inserted rows.

Hope this helps.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:Shums
ID: 41754939
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

Open in new window

How I am gonna change there?
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41754957
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

Open in new window

0
 
LVL 1

Author Comment

by:Shums
ID: 41754975
I already messed it up.

I will come back to you soon.

Thanks a lot for your help.

Kind Regards,
0
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41755191
Hi Shums,

Another simple way to tackle this issue is, assign the value directly to a cell onto the sheet and refer that cell with the help of a formula into the desired cell.

So in the code below, you are assigning the value from the form control to say J2 like this.....
If CurrencyComboBox.value = "$" Then
    Ws.Range("I2").value = "$"
    Ws.Range("J2").value = ""
ElseIf CurrencyComboBox.value = "AOA" Then
    Ws.Range("I2").value = "AOA"
    Ws.Range("J2").value = ExchangeRate.value
End If

Open in new window


Then in the desired cell (i.e. whatever the H76 would be in your case dynamically) use the simple formula below...
=$J$2

Open in new window


Does that make sense?
0
 
LVL 1

Author Comment

by:Shums
ID: 41755376
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

Open in new window


I think eRow -1 deletes the last two rows.
0
 
LVL 1

Author Comment

by:Shums
ID: 41755400
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

Open in new window

Aging-Analysis-Test.xlsm
0
 
LVL 1

Author Comment

by:Shums
ID: 41755512
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

Open 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

Open 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
0
 
LVL 1

Author Closing Comment

by:Shums
ID: 41758008
Thanks Neeraj for your assistance in closing this project.

See you soon with other project.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now