Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert Delete Rows as per count.

Posted on 2016-08-11
16
Medium Priority
?
97 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 32

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 52

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 27

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 32

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 2000 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 32

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 27

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 27

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 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 2000 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
 
LVL 27

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 32

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 27

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 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 2000 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 27

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 27

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 27

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 27

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

715 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