VBA Adding Row Button

Question

I have VBA coding to add row and it copy the row no 8 together with format & formula. I also create Input Box to add how many number of row i needed to be added.

 Sub RectangleRoundedCorners7_Click()
Dim howMany As Long
howMany = InputBox("How many rows?")
Dim i As Long
For i = 1 To howMany
Range("A8:A9").Select
Range("A8").Activate
   With ActiveCell.EntireRow
      .Copy
      .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
       On Error Resume Next
      .Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
      Application.CutCopyMode = False
      On Error GoTo 0
   End With
   Next
   
End Sub

The problem is, when I added the new row, the row will be below row number 8. Which make the item that I wrote down in cell before added new row go down and create spaces just like images.

Sample what happen if I click button add row & enter "2" in the input box. Two new row added at row number 9 & 10.

What I want is the new added row to always to be on top of row that contain word "Space". Meaning to say, new row should always be added to the lowest of my table but before row Contain word "space" or row "Total".

This is what I want when I click add new row button

Please Help. Thank You.

Right now, your code will always insert a new row after row 8. You want to add a new row after the last row of data. Here is a question about finding the last row: https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba — TehDrunkSailor, Aug 01 '23 at 11:27

score 0 · Answer 1 · answered Aug 01 '23 at 13:42

0

First of all avoid the Select and Activate methods. Most of the times, they provide nothing. Just mention directly the cell that you need. In this case you need the row of the last cell containg data in column A.

This statement: Range("A1").End(xlDown) will return from column A, the last cell that contains data, under 2 conditions.

Cell A2 is not empty.
Data in column A are continuous, that is there no blank cells in the middle.

As far as I can see, your data meets these conditions, so this will do the work.

Sub RectangleRoundedCorners7_Click()
Dim howMany As Long
Dim i As Long
    howMany = InputBox("How many rows?")
    With Range("A1").End(xlDown).EntireRow
        For i = 1 To howMany
            .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Next
    End With
End Sub

answered Aug 01 '23 at 13:42

mits

876
3
11
20

I want the adding row to copy cell together with it format & formula. – Wafee89 Aug 02 '23 at 02:20
Btw. thanks to you. it help a lot. i made bit amendment from your code as below. now it works.. Sub RectangleRoundedCorners7_Click() Dim howMany As Long Dim i As Long howMany = InputBox("How many rows?") With Range("A8").End(xlDown).EntireRow For i = 1 To howMany .Copy .Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove .Offset(1).SpecialCells(xlCellTypeConstants).Value = "" Application.CutCopyMode = False On Error GoTo 0 Next End With End Sub – Wafee89 Aug 02 '23 at 02:22

VBA Adding Row Button

1 Answers1