VBA Delete blank rows with a formula but has no value

[clazzic]

Hi All,

So my scenario is i have a range e.g. A3: BA1000 that is filled with formulas. the formulas will blank out when there is no value. In all cases all the rows with value will be at the top.
just looking to see if there is a macro that allows me to find all the rows that has no value and delete?

or is there an option where i can just select my range that has a value and not blanks?

thanks in advance

 

[svendiamond]

find all the rows that has no value and delete

Do you want to delete rows in which every cell is blank? Or delete rows where even one cell is blank?

option where i can just select my range that has a value and not blanks

You could use AutoFilter to filter out blanks... but again I would repeat my question.

 

[clazzic]

Yep my initial query is to only delete rows when every cell has no value. (to remove the formulas)

 

[svendiamond]

Here's one way to do it:

Sub deleteBlankRows()


Dim lastRow As Long, i As Long


With Sheets("Sheet1")
    lastRow = .UsedRange.Rows(.Rows.Count).End(xlUp).Row
    For i = lastRow To 2 Step -1
        If WorksheetFunction.CountA(.Rows(i)) = 0 Then
            .Rows(i).Delete
        End If
    Next i
End With


End Sub

But if you have a lot of data/formulas it may be slow. So if it's slow let me know and we'll find another way.

 

[clazzic]

i tried, the code,

changed the Sheet to reference my sheet

there was no errors but nothing happens.

 

[svendiamond]

Oh whoops forgot about your formulas
Change the COUNTA to COUNTIF and check for blanks.

WorksheetFunction.Countif(.Rows(i), """") = 0 Then

 

[clazzic]

yep changed it, it actually took a while to complete the run, as it constantly recalculates.

But it deletes everything, even the cells with value in them.

 

[svendiamond]

Sorry about that I wasn't testing it. Here this will work:

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

Of course this is assuming your data starts in column A and uses the last used column in the worksheet as a reference point for counting blanks.

 

[clazzic]

Awesome thanks Sven,

it does the function exactly how i wanted. Only problem now is that it takes too long to complete approx 1 min.

This needs to be instantaneous. can it be done?

 

[svendiamond]

Sort first, so that the blank rows go to the bottom. But yeah deleting cells always slows it down. You can try turning off screen updating, etc first and that should help, if you haven't already done that.