Insert blank row under the selected row if not empty

Question

I want to insert a blank row if the selected row is not empty and transfer the data there.

I am able to select a specific row (x), after I need to insert the blank row under if there is data in the columns 4, 5, 6 and 7. I need these new data to migrate to the row under.

Private Sub CommandButton1_Enter()

    Dim emptyRow As Long
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ActiveSheet.Name = "Micrux"

    Dim x As Long
    Dim y As Long
    Dim found As Boolean

    With Sheets("Micrux")
        x = .Range("A" & .Rows.Count).End(xlUp).Row
        For y = 1 To x
            If .Cells(y, 1).Text = ComboBox1.Value Then
                found = True

               .Cells(y, 4) = TextBox1.Text
               .Cells(y, 7) = TextBox2.Text
               .Cells(y, 6) = TextBox3.Text
               .Cells(y, 5) = ComboBox2.Value

            End If
        Next y
    End With

    Unload Me
End Sub

CDP1802 · Accepted Answer · 2020-02-02 09:31:24Z

I have assumed that if there is no match the data should be added below the last row. The search direction is from the bottom upwards so that if there is a block of records with the same colA value, the new record is added below the block. Note I have used the _Click method rather than _Enter. The message boxes are to show you the rows updated, you can comment them out if not required.

Take a look at the object model docs for the insert and find methods on range objects.

Private Sub CommandButton1_Click()

Dim emptyRow As Long
Dim ws As Worksheet
Set ws = ActiveSheet
ActiveSheet.Name = "Micrux"

Dim iLastRow As Long, iFound As Long
Dim rng, bEmpty As Boolean, c As Integer
bEmpty = True

With ws
   iLastRow = .Range("A" & .Rows.Count).End(xlUp).Row

   ' search column starting at bottom
   Set rng = .Range("A1:A" & iLastRow + 1).Find(ComboBox1.Value, _
        After:=.Range("A" & iLastRow + 1), _
        LookIn:=xlValues, _
        lookat:=xlWhole, _
        searchorder:=xlByRows, _
        SearchDirection:=xlPrevious)

   If rng Is Nothing Then
       iFound = iLastRow + 1 ' add to end
   Else
       iFound = rng.Row
       ' check empty
       For c = 4 To 7
         If Len(.Cells(iFound, c)) > 0 Then bEmpty = False
       Next
       ' insert if not empty
       If bEmpty = False Then
          iFound = iFound + 1
         .Cells(iFound, 1).EntireRow.Insert xlShiftDown
         MsgBox "Row inserted at " & iFound, vbInformation
       End If
   End If
   ' transfer data
   .Cells(iFound, 1).Value = ComboBox1.Value
   .Cells(iFound, 4).Value = TextBox1.Text
   .Cells(iFound, 7).Value = TextBox2.Text
   .Cells(iFound, 6).Value = TextBox3.Text
   .Cells(iFound, 5).Value = ComboBox2.Value

   MsgBox "Data copied to " & iFound, vbInformation

End With

End Sub

Hi CDP1802, Thanks a lot, your code works perfectly and it was exactly what I needed!!!

jcrizk · Accepted Answer · 2020-02-01 22:17:08Z

Let me know if this works for you. Your goal wasn't entirely clear to me, so if it doesn't address your specific goal then let me know.

I left comments in the code to explain what I'm doing.

I tested out this code, and I think it's doing what you want. I used constants instead of reading from textboxes because it's easier for me to test, so don't just copy/paste everything verbatim and expect it to work exactly as you're intending it to. You'll need to modify some parts to suit your needs.

Option Explicit

Public Sub test()
    'i prefer to keep all my variable declarations at the top
    'unless i have a specific reason for not doing so
    Dim emptyRow As Long
    Dim ws As Worksheet
    Dim y As Long
    Dim wsHeight As Long
    Dim found As Boolean

    'just some constants i made to make testing easier for me
    Const wsName As String = "Micrux"
    Const combo1Val As String = "some text"
    Const textbox1Val As String = "textbox1 text"
    Const textbox2Val As String = "textbox2 text"
    Const textbox3Val As String = "textbox3 text"
    Const combo2Val As String = "combo2 text"

    'dont set references to sheets like this
'    Set ws = ActiveSheet
'    ActiveSheet.Name = "Micrux"

    'this is better method
    Set ws = ThisWorkbook.Worksheets(wsName)
    'or alternatively this works too
'    Set ws = ThisWorkbook.Worksheets(someWorksheetNumber)

    With ws
        'descriptive variables are easier to read than non-descriptive
        'variables
        wsHeight = .Range("A" & .Rows.Count).End(xlUp).Row

        'you'll need to keep changing wsHeight, so a for loop
        'won't suffice
        y = 1
        While y <= wsHeight
            If .Cells(y, 1).Value = combo1Val Then
                'dont assign values like this
'                .Cells(y, 4) = textbox1Val
'                .Cells(y, 7) = textbox2Val
'                .Cells(y, 6) = textbox3Val
'                .Cells(y, 5) = combo2Val

                'assign values like this
                .Cells(y, 4).Value = textbox1Val
                .Cells(y, 7).Value = textbox2Val
                .Cells(y, 6).Value = textbox3Val
                .Cells(y, 5).Value = combo2Val

                'insert a blank row
                .Cells(y, 1).Offset(1, 0).EntireRow.Insert shift:=xlDown

                'since you inserted a blank row, you need to also
                'increase the worksheet height by 1
                wsHeight = wsHeight + 1
            End If

            y = y + 1
        Wend
    End With

    'idk what this does but i dont like the looks of it
'    Unload Me
End Sub

Hope it helps

Hi jcrizk, Thanks for the help! I tried your code and it copied the data on every row down and made my excel crash! ^_^

Collectives™ on Stack Overflow

Insert blank row under the selected row if not empty

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related