No Help on this one? Thanks Jay! Exactly what I needed...

Tom Schreiner · Apr 9th, 2002, 09:05 PM

Hi everyone!
I've recieved wonderful help here and I doubt this one will be too tough at all.

I have learned how to find the next available row with formulas I have gotten from this room such as .End(xlUp).Row).Rows.Count.

How do I apply this to find the next available column?
I searched and could find no reference to an xlAcross?

The criteria would be the Cell located in row 5 = Nothing to decide if that column is the next column available.

For Example:
If each cell in Range("A5:E5") contained data, but no data in F5, which formula would give me the column number or address of COlumn 'F'?

Thanks again!
Tom

[ This Message was edited by: TsTom on 2002-04-09 21:41 ]

[ This Message was edited by: TsTom on 2002-04-09 22:37 ]

Jay Petrulis · Apr 9th, 2002, 10:55 PM

Hi Tom,

Here are two ways:

lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column

With ActiveSheet
.UsedRange
lastcolumn = .Cells(xlCellTypeLastCell).Column
End With

Bye,
Jay

Tom Schreiner · Apr 9th, 2002, 11:37 PM

Thanks Jay!
That is exactly what I was looking for...
Tom

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 09:05 PM	#1
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi everyone! I've recieved wonderful help here and I doubt this one will be too tough at all. I have learned how to find the next available row with formulas I have gotten from this room such as .End(xlUp).Row).Rows.Count. How do I apply this to find the next available column? I searched and could find no reference to an xlAcross? The criteria would be the Cell located in row 5 = Nothing to decide if that column is the next column available. For Example: If each cell in Range("A5:E5") contained data, but no data in F5, which formula would give me the column number or address of COlumn 'F'? Thanks again! Tom [ This Message was edited by: TsTom on 2002-04-09 21:41 ] [ This Message was edited by: TsTom on 2002-04-09 22:37 ]

Apr 9th, 2002, 10:55 PM	#2
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi Tom, Here are two ways: lastcolumn = Cells(2, Columns.Count).End(xlToLeft).Column With ActiveSheet .UsedRange lastcolumn = .Cells(xlCellTypeLastCell).Column End With Bye, Jay

Apr 9th, 2002, 11:37 PM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Thanks Jay! That is exactly what I was looking for... Tom