Delete Entire Row For All Empty Cells In Column

[johnreid7477]

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
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


Private Sub harga1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

harga1 = Format(harga1, "#,#.00")

End Sub


And that's my all code in userform


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.

[patel]

question 1 (delete rows if blank) : i'm using this code
Worksheets("Data").Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete[B] but i have little problem if not blank this run-time error 1004 .....
your code works well on my test workbook (excel 2010), also with not blank cells

[johnreid7477]

your code works well on my test workbook (excel 2010), also with not blank cells

maybe my code put wrong line ....


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

and can i'm input two columns M & N ? i'm noobs VBA must using columns("M:N") or columns("M"),columns("N) or other suggest.

Thanks - John

[patel]

your code works well on my test workbook (excel 2010), also with not blank cells
But does not work on your sheets, I don't know why

[johnreid7477]

But does not work on your sheets, I don't know why

i'm got answer from p45cal it's work great.


On Error Resume Next
Worksheets("Data").Columns("M").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

Thanks - John