VBA fill blank cells with value above

P

ProbioT

New member
Joined
Jan 12, 2019
Messages
10
Reaction score
0
Points
1
Excel Version(s)
2007
Hi all,
I have created a user form, which works fine if all boxes are populated.
My intention is not to fill out all boxes each time, just changed data will be transferred to the spread sheet.

In that case it will give me blank cells with no data. I'd like to populate blank cells (with no change in the row) with values from cells above.

Can someone help, please?
Thanks.
 

Attachments

  • Book1.xlsm
    36.2 KB · Views: 18
Try adding this to the end of your submitComandButton_Click()
Code: 
    'Fill blanks from above
    For col = 3 To 11
        If ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, col) = "" Then
            ThisWorkbook.Worksheets("Sheet1").Cells(lastrow + 1, col) = ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, col)
        End If
    Next col
 
Omg... it works like a charm. Thank you so much. I spent hours of researching it on the internet without any success.
Great work!
 
Old thread, I know; The whole submit macro can be condensed:
Code: 
Private Sub submitCommandButton_Click()
With ThisWorkbook.Worksheets("Sheet1")
  lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
  With .Range(.Cells(lastrow + 1, 1), Cells(lastrow + 1, 13))
    .Value = Array(dateTextBox.Value, modelComboBox.Value, zone1_ComboBox.Value, zone2_ComboBox.Value, zone3_ComboBox.Value, zone4_ComboBox.Value, levelComboBox.Value, northComboBox.Value, westComboBox.Value, eastComboBox.Value, southComboBox.Value, materialComboBox.Value, commentTextBox.Value)
    For Each cll In .Cells
      If Len(cll.Value) = 0 Then cll.Value = cll.Offset(-1).Value
    Next cll
  End With
End With
End Sub
 
You must log in or register to reply here.
Back
Top