Insert number of rows based on logic statement

Gents,

please help me to make VBA that can search in values in Column B, and add number of rows below each cell satisfies the below:

  • if Value in B > 0 , and  $C$1 – value in column B = 1 , then add 1 row below the cell
  • if Value in B > 0 , and  $C$1 – value in column B = 2 , then add 2 row below the cell
  • if Value in B > 0 , and  $C$1 – value in column B = 3 , then add 3 row below the cell

and So on.

the values of new rows added should be the same as the original rows they added below,  A=A-1 , C=C-1, D=D-1….. but the values B should be as below:

  • value of B (first row added) equal to $C$1 – value in column B (original)
  • value of B (Second row added) equal to $C$1 – value in column B (first row added)
  • value of B (Third row added) equal to $C$1 – value in column B (Second row added)

and so on.

Hope my request is clear, and I highly appreciate you cooperation.

 

 

 

Top Contributor Asked on August 5, 2018 in VBA.
Add Comment
2 Answer(s)
Best answer

I am not sure if this is what you are looking for but test it out anyway.

RE: Insert number of rows based on logic statement

This is before the macro is run.


RE: Insert number of rows based on logic statement

This is after the macro is run.

 

Sub AddRows()
Dim con As Double ' con = Constant
Dim ac As Double ' ac = activecell
Dim row1 As Double ' ac = activerow plus1
Dim i As Integer
Dim r As Integer ' active row number
Dim h As Double
Dim lr As Double
Application.ScreenUpdating = False
lr = Cells(ActiveSheet.Rows.Count, 2).End(xlUp).Offset(1, 0).Select
ActiveCell = "stp"
con = [c1]
[B1].Select
Do
ac = ActiveCell.Value - con
r = ActiveCell.Row
row1 = r + 1
If ActiveCell.Value > 0 Then
For h = 1 To ac
Rows(row1).EntireRow.Insert
row1 = row1 + 1
Next h
ActiveCell.Offset(1).Select
End If
Do
If ActiveCell.Value = "stp" Then Exit Do
If ActiveCell.Value <> "" Then Exit Do
ActiveCell.Offset(1).Select
Loop
If ActiveCell.Value = "stp" Then Exit Do
Loop
ActiveCell = ""
[a1].Select
End Sub
Expert Answered on August 8, 2018.
Add Comment

Dear Hyside2, I really  appreciate your cooperation  and helpful answer.

Thanks so much.

Top Contributor Answered on August 10, 2018.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.