Having Trouble Running a Simple Blank Row Deleting

Question

I'm new to Macros but I suspect something else may be at issue here.

Here is the simple macro to delete blank rows in a multi-sheet workbook.Yes the sheet in question here is sheet number 9.

Sub FnDelete­BlankRows()
Dim mwb As Work­Book
Set mwb = Active­Work­Book
For x = mwb.Sheets(“Sheet1”).Cells.SpecialCells(xlCellTypeLastCell).Row 1 Step –1
If WorksheetFunction.CountA(mwb.Sheets(“Sheet1”).Rows(x)) = 0 Then
mwb.Sheets(“Sheet9”).Rows(x).Delete
End If
Next
End Sub

The error which comes up is "User-defined type not defined"

Earlier I had tried the code below and received a "Syntax Error". I tried Googling and did all the standard fixes(ensuring macros were enabled,designer turned off etc and I even saved my sheet as an xltm)

Sub RemoveRows()
Dim lastrow As Long
Dim ISEmpty As Long
Count how many records in the list. This is done so that the Do loop has a finish point.

lastrow = Application.CountA(Range(“A:A”))
‘Start at the top of the list
Range(“A1″).Select

Loop until the end of the list
Do While ActiveCell.Row < lastrow
Assign number of non empty cells in the row
ISEmpty = Application.CountA(ActiveCell.EntireRow)
If ISEmpty = 0 then delete the row, if not move down a cell into the next row
If ISEmpty = 0 Then
ActiveCell.EntireRow.Delete 
Else
ActiveCell.Offset(1, 0).Select
End If
LoopEnd Sub

As much as I would enjoy learning the finer points of VBA I would really like to just learn how to make a macro work out of the box with minimal customization.

Thanks

Answer 1

Try this- make sure to change Sheet1 to what ever sheet you are working on.

---

Tested Excel 2010

Option Explicit
'// Delete blank Rows
Sub xlDeleteBlankRows()
   Dim xlWb As Workbook
   Dim i As Long
   Set xlWb = ActiveWorkbook
   For i = xlWb.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).row To 1 Step -1
      If WorksheetFunction.CountA(xlWb.Sheets("Sheet1").Rows(i)) = 0 Then
         xlWb.Sheets("Sheet1").Rows(i).Delete
      End If
   Next
End Sub

---

Option Explicit
Sub xlRemoveRows()
    Dim lastrow As Long
    Dim ISEmpty As Long

    '// Count how many records in the list. This is done so that the Do loop has a finish point.
    lastrow = Application.CountA(Range("A:A"))

    '// Start at the top of the list
    Range("A1").Select

    '// Loop until the end of the list
    Do While ActiveCell.Row < lastrow

    '// Assign number of non empty cells in the row
    ISEmpty = Application.CountA(ActiveCell.EntireRow)
            '// If ISEmpty = 0 then delete the row, if not move down a cell into the next row
            If ISEmpty = 0 Then
            ActiveCell.EntireRow.Delete
            Else
        ActiveCell.Offset(1, 0).Select
        End If
    Loop
End Sub

See helpful article at MSDN Getting Started with VBA in Excel 2010 & 2013

---

Option Explicit
Sub xlDeleteRow()
    Dim lngRow, lngCrnt, lngCol As Long
    Dim xlBln As Boolean
    Application.ScreenUpdating = False
    lngRow = Cells(Rows.count, "A").End(xlUp).Row
    For lngCrnt = lngRow To 1 Step -1
        xlBln = False
        For lngCol = 1 To 2
            If Cells(lngCrnt, lngCol).Value <> vbNullString Then
                xlBln = True
                Exit For
            End If
        Next lngCol
        If Not xlBln Then Rows(lngCrnt).Delete
    Next lngCrnt
    Application.ScreenUpdating = True
End Sub