Delete row of last empty cell

  • Please, dear MrExcel, how to find last empty cell or last NOT empty cell from three columns, then delete rows? Using VBA.
    [TABLE="class: cms_table"]

    [tr]


    [td]

    john

    [/td]


    [td]

    MArk

    [/td]


    [td]

    Julia

    [/td]


    [/tr]


    [tr]


    [td]

    Johnson

    [/td]


    [td]

    john

    [/td]


    [td]

    Mark

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td]

    John

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    How to delete empty rows from third column to down, actually I have B2:B15, K2:K15, T2:T15, I need to delete rows from 5:15. But sometimes there will be more data in B or K column, I need to find last non empty cell in these ranges and then delete rows:15
    Thank you in advance!



    Code:

    Code
    1. Dim g As Long, r As Long, u As Long
    2. For g = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
    3. For r = Cells(Rows.Count, "K").End(xlUp).Row To 1 Step -1
    4. For u = Cells(Rows.Count, "T").End(xlUp).Row To 1 Step -1
    5. If Cells(g, "B") = "" Then Cells(g, "B").EntireRow.Delete xlUp
    6. ElseIf Cells(r, "K") = "" Then Cells(r, "K").EntireRow.Delete xlUp
    7. ElseIf Cells(u, "T") = "" Then Cells(u, "T").EntireRow.Delete xlUp
    8. Next u
    9. Next r
    10. Next g


    This code not working, it watches only B, and then deletes all data from rows [Blocked Image: http://www.mrexcel.com/forum/images/smilies/frown.png]
    Sorry bad english[Blocked Image: http://www.mrexcel.com/forum/images/smilies/icon_smile.gif]

  • Re: Find last empty cell from range, then delete rows?


    Maybe this code, i dont know....


    Code
    1. Case 1 'Find last row
    2. On Error Resume Next
    3. FindLast = rFind.Find(What:="*", _
    4. After:=rFind.Cells(1), _
    5. LookAt:=xlPart, _
    6. LookIn:=xlFormulas, _
    7. SearchOrder:=xlByRows, _
    8. SearchDirection:=xlPrevious, _
    9. MatchCase:=False).Row
    10. On Error GoTo 0


    But, i need, not from all columns, i need from ranges B2:B15, K2:K15, T2:T15 :loo:

  • Re: Find last empty cell from range, then delete rows?


    Have a try with this too:

  • Re: Find last empty cell from range, then delete rows?


    Proper data: [ATTACH=CONFIG]70100[/ATTACH]
    Notice how is only deleted rows(cells) from B column, K and T columns in this example were with less populated cells then B col.
    Problem is When i have more polpulated cells in K or T, and less in B, then VBA code will delete data :(

    Code
    1. Dim j As Long
    2. For j = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
    3. If Cells(j, "B") = "" Then Cells(j, "B").EntireRow.Delete xlUp
    4. Next j



    Correction: my range B2:B16(15cells), K2:K16, T2:T16 I don't need any code to delete rows to row 15.


    Code
    1. If g < 15 Then Range(Cells(g + 1, 1), Cells(15, 1)).EntireRow.Delete 'delete to row 15

    It is automatic. I mean, every report will have populated 1st row etc. I first insert 15 rows, then code copy data to report, and then i need to check empty cells B,K,T...

  • Re: Find last empty cell from range, then delete rows?


    Code
    1. Dim g As Long, r As Long, u As Long
    2. For g = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
    3. For r = Cells(Rows.Count, "K").End(xlUp).Row To 1 Step -1
    4. For u = Cells(Rows.Count, "T").End(xlUp).Row To 1 Step -1
    5. If Cells(g, "B") = "" Then Cells(g, "B").EntireRow.Delete xlUp
    6. ElseIf Cells(r, "K") = "" Then Cells(r, "K").EntireRow.Delete xlUp
    7. ElseIf Cells(u, "T") = "" Then Cells(u, "T").EntireRow.Delete xlUp
    8. Next u
    9. Next r
    10. Next g


    This is my first code, edited. It's not working for me. I received two red cards(Infraction) from moderator, because, i cannot edit my original post, I'm using Win7 IE11

  • Re: Find last empty cell from range, then delete rows?


    Quote from rollis13;776748

    Have a try with this too:


    Thank you for your time. Code is not working, it did not deleted yellow rows: [ATTACH=CONFIG]70101[/ATTACH]




  • Re: Find last empty cell from range, then delete rows?



    Compile error, code not working :(

  • Re: Find last empty cell from range, then delete rows?


    Please attach a working example (no personal data) of the sheet, not an imagine that is useless.
    If I now have correctly understood your requirement use this:

  • Re: Find last empty cell from range, then delete rows?


    I dont have PC, i'm home on Lumia 620 screen small, going on work on monday. In my code i insert 15 row, i don' t need to delete to the row 15, In my picture CapturePROPER2 it is clear that code don't work. Yellow rows wasn't deleted.

  • Re: Find last empty cell from range, then delete rows?


    So maybe this:

  • Re: Delete row of last empty cell


    rollis13 , thank YOU SO MUCH, code is working. But, ... what if i accidentally press to copy report but all B,K,T rows(cells) are empty?
    Here is my original code, it's big, i don't know if it needs "calculation manual" or Application.Screen updating.... I don't know VBA, code is from everyware:OzGrid, MrExcel, ExcelIsFun,....
    Copy report sheet is "Izvjestaj_SVE", 14 days report backup is sheet "Izvjestaj_BACKUP", sheet"......STANJE" is origin of report.


    [ATTACH=CONFIG]70117[/ATTACH][ATTACH=CONFIG]70118[/ATTACH]

  • Re: Delete row of last empty cell


    Cannot edit first post.
    Here all links:http://answers.microsoft.com/e…d8-4840-ac53-55cb737938b4


    http://www.mrexcel.com/forum/e…nge-then-delete-rows.html


    http://www.excelforum.com/exce…nge-then-delete-rows.html



    I appologise rollis13, it's working. My mistake in code: it shoud be B1:B16, K1:K16, T1:T16
    Should i put Calculation.manual and ApplicationScreenUpdating=False?
    Thank YOu ALL , God Bless Us All.

  • Re: Delete row of last empty cell


    While running my code no Calculation is needed so it's useless to Stop/Start; ScreenUpdating is only for screen flickering but if to much it could be time-wasting so in this case it might be useful to limit it.