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
    Dim g As Long, r As Long, u As Long
                    For g = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
                    For r = Cells(Rows.Count, "K").End(xlUp).Row To 1 Step -1
                    For u = Cells(Rows.Count, "T").End(xlUp).Row To 1 Step -1
                        If Cells(g, "B") = "" Then Cells(g, "B").EntireRow.Delete xlUp
                        ElseIf Cells(r, "K") = "" Then Cells(r, "K").EntireRow.Delete xlUp
                        ElseIf Cells(u, "T") = "" Then Cells(u, "T").EntireRow.Delete xlUp
                                        Next u
                                Next r
                        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
    Case 1 'Find last row
                 On Error Resume Next
                 FindLast = rFind.Find(What:="*", _
                                 After:=rFind.Cells(1), _
                                 LookAt:=xlPart, _
                                 LookIn:=xlFormulas, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlPrevious, _
                                 MatchCase:=False).Row
                 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
    Dim j As Long
                    For j = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
                        If Cells(j, "B") = "" Then Cells(j, "B").EntireRow.Delete xlUp
    
    
                        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
    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
    Dim g As Long, r As Long, u As Long
     For g = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -1
     For r = Cells(Rows.Count, "K").End(xlUp).Row To 1 Step -1
     For u = Cells(Rows.Count, "T").End(xlUp).Row To 1 Step -1
     If Cells(g, "B") = "" Then Cells(g, "B").EntireRow.Delete xlUp
     ElseIf Cells(r, "K") = "" Then Cells(r, "K").EntireRow.Delete xlUp
     ElseIf Cells(u, "T") = "" Then Cells(u, "T").EntireRow.Delete xlUp
     Next u
     Next r
     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


    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!