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