VBA to delete rows if cell is blank

  • Qualityguy2
  • Wander is marked as Solved.
    • #1

    Hello

    I am struggling to edit unloaded rows. Here is my situation:

    I have data in rows observed by the user. Data begins in row 10 and can draw near to row 55 if requisite. In row 56 I have formulas to sum the information in columns, and row 57 is for comments. Well-nig of the time users only when use 10-20 rows for information. BUT, they are lazy and I give the sack non get them to delete blank rows. So I am in need of any help with the VBA code to detect if the "r" run-in column 2 is blank shell, then delete the row, and shift rows up. I am victimisation a for-close loop with r as my replication, beginning at row 10, and going to last words (as plant by xcel ) minus 2 (the totals and comments). when gushing, i check and information technology loops done each wrangle, and identifies sort out start row 10, last row, 57, and mlr (go row of data) row 55.

    BUT--- it deletes rows and leaves roughly 20 blank rows, and deletes my comments and totals rows?

    Any thoughts, please?

    my loop cipher is below :

    • #2

    Re: VBA to cancel rows if cell is lacuna

    To trial run for a blank cell and delete it, you must melt your loop from the last run-in to the first row. ie. Reverse order. An example is as follows which looks in Column B for blanks

    1. Dim lr as yearlong
    2. lr = range("B" & rows.numeration).end(xlup).quarrel
    3. Dim i as long-dated
    4. For i = Lr to 2 step -1 'loops from last row to row 2
    5. If range("B" & i) = "" then roll("B" &A; i).entirerow.delete
    6. Next i

    Amend your code to do a vacat loop for that section of your codification.

    • #3

    Re: VBA to delete rows if cell is blank

    Alan

    Thanks for the cypher and the info. As a novice, i am always grasping for answsers. So, why does excel for you to work prepared from behind?

    I would cogitate that for clean and efficient code, I could test the cellular phone As I am looping through with my process, and if it is blank, delete the wrangle.

    Is this the reason?
    if information is present in row 10, 11,12;
    the first row deleted would be 13, and so row 14 becomes 13 - and since the loop has incremented it now is looking at row 14, only leaving the "new" row 13 .

    Is this correct?

    • #4

    Re: VBA to delete rows if cell is empty

    1. [COLOR=#333333]the first row deleted would live 13, then row 14 becomes 13 - and since the loop has incremented information technology today is looking at quarrel 14, only leaving the "new" row 13 . [/COLOR]

    Your analysis skills are happening target.:)

    • #5

    Rhenium: VBA to delete rows if cell is blank

    In that location is no need to loop topology to delete rows.

    Remove the "Else" bit of your If statement and then use this after the For.......Next intertwine

    1. With Sheets("Report")
    2. Dim lRow As Long
    3. lRow = .Cells(.Rows.Count, 2).End(xlUp).Dustup
    4. .Cells(2, 2).Resize(lRow -1).SpecialCells(4).EntireRow.Delete
    5. End With


    The end of your cypher should look the like this

    SpecialCells(4) means cells that are devoid, so this codification deletes all rows where column B is blank in one become.