Last Column Using VBA

[Topdrop18]

I have searched through the website and even Bill Jelen's book, but I still cannot find the answer as to how to write the code to find the last column with data in it. I have tried defining a range as seen below, but it's still not working. Any suggestions?

finalcolumn = Cells(1, Columns.Count).End(xlToLeft).Column

 

[schielrn]

Welcome to the Board!

This will find the last column based on row 1, is that the row you are wanting to find the last column based on?

Maybe the column it is currently returning has a null string in it? What does this return and waht are you expecting?

msgbox Cells(1, Columns.Count).End(xlToLeft).Column

 

[Legacy 14611]

Try:

lstcol = Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious, LookIn:=xlValues).Column

 

[Topdrop18]

So those really aren't giving me what I need. I essentially want to be able to define what the last row is so that I can then use a loop to run a formula, when the number of columns may change. Does that make sense?

 

[Legacy 14611]

If you're clear about what you want from the start, you're much more likely to get it.

The line of code I suggested above does exactly what you initially specified.

 

[VoG]

You asked for the last column when I think you meant row

LastRow = ActiveSheet.UsedRange.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row