VBA to delete rows with a formula that equals blank
Results 1 to 6 of 6

Thread: VBA to delete rows with a formula that equals blank
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to delete rows with a formula that equals blank

    I've created a button that incorporates a bunch of functions. Everything is working, but the process started taking an extremely long time after I added the function below. What I'm do with this function is deleting all of the rows that have formulas in them that end up equaling a blank cell. Does anyone have any ideas on a better approach?

    Dim i As Long, finalRow As Long
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    With Worksheets("Backlog Report (Regular)")
    For i = finalRow To 2 Step -1
    If Range("C" & i).Value = "" Then
    Range("C" & i).EntireRow.Delete
    End If
    Next i
    End With

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,456
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA to delete rows with a formula that equals blank

    You can help speed things up by temporarily disabling calculations and screen updates, i.e.

    Code:
    Dim i As Long, finalRow As Long
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row
    With Worksheets("Backlog Report (Regular)")
    For i = finalRow To 2 Step -1
    If Range("C" & i).Value = "" Then
    Range("C" & i).EntireRow.Delete
    End If
    Next i
    End With
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,117
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA to delete rows with a formula that equals blank

    Quote Originally Posted by NRIANS2123 View Post
    What I'm do with this function is deleting all of the rows that have formulas in them that end up equaling a blank cell.
    There might be a quick non-looping way to do this, but it depends on what your formula is returning when it is not returning a blank (""). So, what kind of values does your formula return?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to delete rows with a formula that equals blank

    It's a simple formula carrying data over from another sheet with an IF function to return a blank cell when there's no data to carry over. What I'm doing is taking a report from our ERP system and dumping the report into one sheet. The second sheet that has the formula below is simply taking the dumped data and pulling out the information I want to see.

    =IF(Report!B27=0,"",Report!B27)

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to delete rows with a formula that equals blank

    Thanks. That did help speed it up some.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,117
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA to delete rows with a formula that equals blank

    Quote Originally Posted by NRIANS2123 View Post
    It's a simple formula carrying data over from another sheet with an IF function to return a blank cell when there's no data to carry over. What I'm doing is taking a report from our ERP system and dumping the report into one sheet. The second sheet that has the formula below is simply taking the dumped data and pulling out the information I want to see.

    =IF(Report!B27=0,"",Report!B27)
    Okay, from that formula I conclude the values that are not blanks are numbers. If that is correct, then see if this macro is any faster than what you have now...
    Code:
    Sub DeleteFormulaRowsDisplayBlanks() On Error GoTo NoBlanks Range("C2", Cells.Find("*", , xlFormulas, , xlRows, xlPrevious)).SpecialCells(xlFormulas, xlTextValues).EntireRow.Delete NoBlanks: End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •