Last used column with data - ignoring formulas

[vaclavvesely]

Hi, I have a spreadsheet where is a table filled with formulas returning "" by default and I need macro which will give me a last used cell/column containing any value ignoring cells containing ""

now I use this:

mycell = Cells(3, .Cells(myCell.Row, .Columns.Count).End(xlToLeft).Column)

that returns just end of the table - last column, because all cells are containing "". I need to return number of column containing visible data. Is there any solution?

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

   MyCell = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column

 

[vaclavvesely]

Not working, it returns last column of table even if its clear (without value or any formula), cells in the table contains only formatting of the table

 

[Fluff]

Does your table have headers?

 

[vaclavvesely]

Yes, it has header.
I can send a test file, is here that possibility? Would be more clear what exactly I need.

 

[vaclavvesely]

https://framapic.org/3THkoBpcp5ja/ze6IkqQKCgNR.PNG

 

[Fluff]

This will return the last cell with a value in row 3

   MyCell = Rows(3).Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column

If that is not what you want you will need to explain what you do want.

 

[vaclavvesely]

On the picture is my table, i have a custom function which runs the macro looking for last used column. on the picture can see that it returns end of the table instead of last used column (there is no visible values in that row) - marked red.

 

[Fluff]

On your picture I cannot see what your function is returning as the cell is blank.
Please explain in words what you are trying to do.

 

[vaclavvesely]

And this is my function, it works fine if there are empty cells without formulas.

Function DATUM_HOTOVO(myCell As Range) As LongApplication.Volatile


    With myCell.Parent


        DATUM_HOTOVO = Cells(3, .Cells(myCell.Row, .Columns.Count).End(xlToLeft).Column)
    End With


End Function

You see that L4 contains a formula returning "" (blank cell with formula)
I4 contains my formula, it returns value from row nr 3 and last used (containing visible data) column - date from header. it works fine if there are blank cells, only a few has an "x" or something inside, but if there is a formula returning "" (not visible value) it returns end of the table (last column at all), so it acts like all cells have visible data, doesn't matter if they are really visible or not. I need to count only with wisible data, ignoring cells which returns "" from formulas.