Posts by B.W.B.

    Re: Find Max number value from text-number cell?


    Input will always be: INV- and /16(or 17,18...). So, now I need to strip all invoice numbers(array) from sheet1(column P), sheet2(col P), sheet3(col P), and then compare them to find max number. All that w/o Indirect or any other volatile funtion, or wrap it inside Index or Aggregate. Also, i need somehow error check when e.g. salesman wrong input invoice number: IN-2016 16. From start, there should be INV-(4 char.) and from back /16(3char.). That would be very complex formula: 1. check is first 4 char=INV- if not display message or #N/A 2.check is any of last 3char="/" if not display message or #N/A, when all that check is Ok then 3. from all trunc last 3char, from that result(INV-2016, INV-2017, INV-2018, INV-2019...) 4. left 4 char to extract numbers, and finally 5.Max(Large) that numbers. All that inside ="INV-"&AGGREGATE(Oh dear God, how wonderful would be to know Excel)&"/16"

    Re: Find Max number value from text-number cell?


    What to say? After 2 litres wine, and in the morning 4 litres water to cure hangover,.... amazing Excelxor! CHOOSE({1\2\3} , must be that I thought on that amazing N(1,(;;;.. formula! Again BIG THANKS!
    My vba solution( not mine,Ozgrid):


    I can't remember why code wasn't working, i think 'cause it's volatile. And in cell formula alongside: ="INV-"&IF(MAX(Table2[INVOICE No.];Table3[INVOICE No.];Table4[INVOICE No.])+1=1;" 0 ";MAX(Table2[INVOICE No.];Table3[INVOICE No.];Table4[INVOICE No.])+1)&"/16" This code and formula should provided me NEXT FREE No for invoice. Don't know what went wrong with all this code&formula solution:(

    Re: Find Max number value from text-number cell?


    mAX is INV-2025/16. Invoice numbers are entered manually, so, i think probably last three "/16" is always the same. There may be error like INV2025/16, so last three for sure. tHANKS! Answer should be INV-2025/16. ="INV-" & yadayada & "/16"

    Re: Find Max number value from text-number cell?


    These three columns are on different sheets, range P9:P109. Problem might be, invoice numbers can be random entered like that INV-2025/16. This sixteen is year, so I want strip this to 2025. Thanks in advance. God bless!

    Hello world!
    Please, i need to find Max number value(MAX invoice number INV-2025/16) from three General(text-number) columns:
    [TABLE="class: grid, width: 400"]

    [tr]


    [td]

    INV-2016/16

    [/td]


    [td]

    INV-2018/16

    [/td]


    [td]

    INV-2017/16

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2021/16

    [/td]


    [td]

    INV-2020/16

    [/td]


    [td]

    INV-2019/16

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2025/16

    [/td]


    [td]

    INV-2022/16

    [/td]


    [td]

    INV-2023/16

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2024/16

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    These columns are on different sheets.
    =INDIRECT("S"&MAX(IF(NOT(ISBLANK(S:S));ROW(S:S)))) no to this formula 'cause it's Volatile!
    {=IF(ISBLANK($P$5:$P$109);ROW($P$5);SUMPRODUCT(MAX((ROW($P$5:$P$109))*($P$5:$P$109<>"")))+1)} this is promising
    =RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))+2) this one for extracting numbers


    INV-2023/16, INV-2024/16, INV-2025/16 which one is Max ??:cat:

    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: 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?



    Compile error, code not working :(

    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?


    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?


    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?


    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:

    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]