Delete Entire Row For All Empty Cells In Column
Hi All,
Need Help about delete row if blank & format accounting in my complicated userform.
question 1 (delete rows if blank) : i'm using this code
Code:
Worksheets("Data").Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
but i have little problem if not blank this run-time error 1004 ..... and can i'm add more for two columns M (harga) & N (unit) ....
question 2 (format accounting) : how do my all harga1,harga2,harga3,...harga10 become "#,#.00" i'm just understand do only one for harga1
Code:
Private Sub harga1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
harga1 = Format(harga1, "#,#.00")
End Sub
And that's my all code in userform
Code:
Private Sub buttonnew_Click()
Dim Cancel As Boolean
Dim keyCol As Range
Dim i As Long
Set keyCol = ThisWorkbook.Sheets("Data").Columns(2)
If Me.noinvoice.Value = "" Then
MsgBox "Please enter a name", vbExclamation, "Invoice"
Cancel = True
End If
If Me.Tanggal.Value = "" Then
MsgBox "Please enter a item", vbExclamation, "Invoice"
Cancel = True
End If
If Me.Costumers1.Value = "" Then
MsgBox "Please enter a costumers name", vbExclamation, "Invoice"
Cancel = True
End If
If Me.ttd.Value = "" Then
MsgBox "Please enter a ttd", vbExclamation, "Invoice"
Cancel = True
End If
If Me.initial.Value = "" Then
MsgBox "Please enter a initial", vbExclamation, "Invoice"
Cancel = True
End If
If Cancel = True Then
'Do nothing
Else
For i = 1 To 10
If Me.Controls("item" & i).Text <> vbNullString Then
With keyCol.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow
.Columns("L").Value = Me.Controls("item" & i).Text
.Columns("N").Value = Me.Controls("unit" & i).Value
.Columns("K").Value = Me.Controls("type" & i).Text
.Columns("M").Value = Me.Controls("harga" & i).Value
.Columns("P").Value = disc1
.Columns("Y").Value = Me.PajakCode.Value
.Columns("S") = "10%"
.Columns("B") = noinvoice
.Columns("C").Value = Me.Tanggal.Value
.Columns("D").Value = Costumers1
.Columns("V").Value = Note
.Columns("X").Value = ttd
.Columns("W").Value = initial
End With
End If
Next i
Range("AA1").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Unload Me
End If
Worksheets("Data").Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("Costumers")
With noinvoice
.Value = Format(Val(Cells(Rows.Count, "B").End(xlUp)) + 1, "0000") & "/SM/" & Format(Month(Date), "00") & "/" & Format(Year(Date), "00")
.Enabled = False
End With
Me.Tanggal.Value = Format(Date, "long Date")
For Each cCostumers1 In ws.Range("AlamatCostumers")
With Me.Costumers1
.AddItem cCostumers1.Value
.List(.ListCount - 1, 1) = cCostumers1.Offset(0, 4).Value
End With
Next cCostumers1
End Sub
Private Sub disc1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
disc1 = Format(Val(disc1.Value) / 100, "##%")
End Sub
Private Sub harga1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
harga1 = Format(harga1, "#,#.00")
End Sub
Private Sub buttonclose_Click()
Unload Me
End Sub
Thanks for any help.
