Your question, your audience. Choose who sees your identityâ€”and your questionâ€”with question security.
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
Ws2.Range("A30:A" & RowDiff + 2).Delete shift:=xlUp
than Ws2.Range("A30" & RowDiff + 2).Delete shift:=xlUp
Regards
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
Aging-Analysis-Test.xlsm
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
Ws2.Range("A25:H25").Copy
Ws2.Range("A25:H82").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
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
How I am gonna change there?
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
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
=$J$2
Ws2.Range("A" & StartRow + minRow - 1 & ":A" & eRow - 1).EntireRow.Delete
Ws2.Columns("H:H").AutoFit
Aging-Analysis-Test.xlsm
LastCell = Ws2.Range("H" & Rows.Count).End(xlUp).row
Ws2.Range("H" & LastCell).Columns.AutoFit
Ws2.Range("A" & StartRow + minRow - 1 & ":A" & eRow - 1).ClearContents
2. If previous count was greater than minRows count (60rows), it doesn't delete correctly for new selection.