Inserting Blank Rows in Excel VBA

Question

Hey I have been writing some code to add a part ID to a spreadsheet off of a user form in Excel VBA. I have been reading through different documentation and can not figure out why no matter what type of method of inserting a row I try it inserts a row with a repeating value instead of a blank one. If anyone knows how to specify blank, other than writing the whole row to blank and then writing my numbers I want after, that would be appreciated.

I have tried both the following lines to add a row

Cells (x+1 ,column).EntireRow.Insert Shift:= xlDown

ws1.Rows(x+1).Insert Shift:=xlDown

This is the function it is used in:

Public Sub Add(IDRange As Range)
          SearchCell = Cells(x, IDRange.Column)
          Cells(x, IDRange.Column).Select
          Do
               If SearchCell = PartID Then
                    MsgBox " this Company Already uses this part"
                    Exit Sub
               ElseIf x <> StopRow Then
                    x = x + 1
                    SearchCell = Cells(x, IDRange.Column)
               End If
          Loop While x <> StopRow And SearchCell <> PartID
          Cells(x + 1, IDRange.Column).EntireRow.Insert Shift:=xlDown
          Cells(x, IDRange.Column).Value = PartID
          MsgBox PartID & " has been added to Adress " & Cells(x, IDRange.Column).Address
          Cells(x, IDRange.Column).Select
End Sub

Bellow is the function that calls the Add Function and where I belive it may be getting the company name from

Private Sub AddPart_Click()
AddPartCounter = 0
Company = UserForm1.CompanyBox.Value
PartID = UserForm1.PartBox.Value

If Company = "" Then
     MsgBox " Please put in the company you would like the part to go under"
ElseIf PartID = "" Then
     MsgBox " Please put in the Part you would like entered"
ElseIf UserForm1.Studs.Value = False And UserForm1.Spreaders.Value = False And UserForm1.Blocks.Value = False And UserForm1.Imma.Value = False Then
     MsgBox "Please select the type of part you are trying to add"
Else
     Dim CurrentCell
     Set CurrentCell = Cells.Find(What:=Company, LookAt:=xlWhole)
     If CurrentCell Is Nothing Then
          MsgBox " Company Not Found "
          Exit Sub
     End If
     x = CurrentCell.Row
     Do
          Set CurrentCell = CurrentCell.Offset(1, 0)
     Loop While CurrentCell.Offset(1, 0) = "" And Not CurrentCell Is Nothing And CurrentCell.Offset(1, 0).Row <> thisvar.Row + 1
     StopRow = CurrentCell.Row

     'If they are trying to add a nut
     If UserForm1.Imma.Value = True Then
          Call Add(Nut_ID_Rng)

     'IF they are trying to add a stud
     ElseIf UserForm1.Studs.Value = True Then
          Call Add(Stud_ID_Rng)

     'If they are trying to add a block
     ElseIf UserForm1.Blocks.Value = True Then
          Call Add(Block_ID_Rng)
     'If they are trying to add a spreader
     ElseIf UserForm1.Spreaders.Value = True Then
          Call Add(Spreader_ID_Rng)
     End If
End If
AddPartCounter = 1
End Sub

I know that the repeating pattern is coming from the insert line through debugging but I can not figure out why I have tried changing variables to numbers and it still did the same thing. This what it looks like with the repeating values. enter image description here

Answer 1

The problem is that you most likely have a value still stored in your clipboard when you execute the Macro. This causes the Excel to paste the string from clipboard into every inserted cell. To fix that, simply add this line of code before running the insert line:

Application.CutCopyMode = False

That will clear your clipboard and allow the inserted rows to be blank.