Hello
is it possible fill blank rows based on userform before add new row before TOTAL row and when filled and there is no blank rows , then should add new row before TOTAL row.
the code will add new row before TOTAL row even there are blanks row , shouldn't do that .
Private Sub CommandButton1_Click()
Dim LR As Long
Dim ws As Worksheet
Set ws = Sheets("GOODS")
With ws
' determine last used cell in column B
LR = .Range("B" & .Rows.Count).End(xlUp).Row
'insert a row before end
.Rows(LR - 1).Insert xlShiftDown, xlFormatFromLeftOrAbove
' copy value to inserted row
.Rows(LR - 1).Value = .Rows(LR).Value
' copy UserForm values to last row
.Range("B" & LR) = Date
.Range("C" & LR) = TextBox1.Text
.Range("D" & LR) = TextBox2.Text
.Range("E" & LR) = TextBox3.Text
.Range("F" & LR) = TextBox4.Text
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
End Sub