VBA Delete Blank Rows Including First Row

[klvn88]

I'm referencing a code that was posted here: https://www.mrexcel.com/forum/excel...lete-blank-rows-formula-but-has-no-value.html

Sub deleteBlankRows()


Dim lastRow As Long, lastCol As Long, i As Long, myBlanks As Long


With Sheets("Sheet1")
    lastRow = .UsedRange.Rows(.Rows.Count).End(xlUp).Row
    lastCol = .UsedRange.Columns(.Columns.Count).End(xlToLeft).Column
    For i = lastRow To 2 Step -1
        myBlanks = Application.WorksheetFunction.CountBlank(.Range(.Cells(i, 1), .Cells(i, lastCol)))
        If myBlanks = lastCol Then
            .Rows(i).Delete
        End If
    Next i
End With


End Sub

This code doesn't appear to run properly if the first row is blank. I think it has something to do with UsedRange. Can someone please tell me where I can address this in the code?

Thank you!

 

[fhqwgads]

maybe change "For i = lastRow To 2" to a 1?

Sub deleteBlankRows()


Dim lastRow As Long, lastCol As Long, i As Long, myBlanks As Long


With Sheets("Sheet1")
    lastRow = .UsedRange.Rows(.Rows.Count).End(xlUp).Row
    lastCol = .UsedRange.Columns(.Columns.Count).End(xlToLeft).Column
    For i = lastRow To 1 Step -1
        myBlanks = Application.WorksheetFunction.CountBlank(.Range(.Cells(i, 1), .Cells(i, lastCol)))
        If myBlanks = lastCol Then
            .Rows(i).Delete
        End If
    Next i
End With


End Sub

 

[Akuini]

Try changing this:

    lastRow = .UsedRange.Rows(.Rows.Count).End(xlUp).Row
    lastCol = .UsedRange.Columns(.Columns.Count).End(xlToLeft).Column

to this:

lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

 

[DanteAmor]

use usedrange instead of rows.count

Sub deleteBlankRows()
    Dim lastRow As Long, lastCol As Long, i As Long, myBlanks As Long
    With Sheets("Sheet1")
        lastRow = .UsedRange.Rows([COLOR=#0000ff].UsedRange.Rows.Count[/COLOR]).Row
        lastCol = .UsedRange.Columns([COLOR=#0000ff].UsedRange.Columns.Count[/COLOR]).Column
        For i = lastRow To 2 Step -1
            myBlanks = Application.WorksheetFunction.CountBlank(.Range(.Cells(i, 1), .Cells(i, lastCol)))
            If myBlanks = lastCol Then
                .Rows(i).Delete
            End If
        Next i
    End With
End Sub

 

[DanteAmor]

This code doesn't appear to run properly if the first row is blank. I think it has something to do with UsedRange. Can someone please tell me where I can address this in the code?

Thank you!

If the first row is empty, then the used range starts in row 2, hence you want to jump to the row according to the row number of the sheet (.rows.count), then this over passes the number of rows in the sheet , that's why it sends the error.

 

[MARK858]

The failure to delete the 1st row is simply (as fhqwgads has posted) that i (which is the row number) can only go to row 2 because of

For i = lastRow To [COLOR="#FF0000"]2[/COLOR] Step -1

.

The usedrange is only being used to determine the last row and last column. Admittedly I wouldn't use it to determine either because of the issues with usedrange, but it is not causing the issue because it isn't being used to determine the start row.

Personally I also wouldn't use COUNTBLANK, I would prefer using WorksheetFunction.Counta = 0.

 

[klvn88]

I didn't expect so many responses! The first response changing the 2 to a 1 resolved the issue. Thank you to all!