View original (French)

Excel VBA: Last Used Column

Solved
Eaheru Posted messages 205 Status Membre -  
 Darnc -
Hello,

I'm looking to find the last used column in an Excel sheet.
The equivalent of the command: 
lastc = Range("IV1").End(xlToLeft).Column

But for columns :)

Thank you in advance!

8 réponses

Réponse 1 / 8
chossette9 Posted messages 6855 Registration date   Status Contributeur Last intervention   1 313
 
Hello,

following the same principle as what you mentioned, Range("IV1").End(xlToLeft).Column will return the last used column.

However, the concepts A65536 and IV1 are only compatible with Excel versions up to 2003. For later versions, the references would be A1048576 and XFD1.

So, here are the codes to retrieve the last row and the last column for all versions: 

for the last row: derlig = Range("A" & Rows.Count).End(xlUp).Row for the last column dercol = Cells(1,Cells.Columns.Count).End(xlToLeft).Column


Thanks to pijaku and to his tip.

Best regards.
--
This guy wasn't very bright, as he was foolish.
33
Eaheru Posted messages 205 Status Membre 20
 
Very interesting! I'm adapting my code right away because I'm using Excel 2007.
0
pierre du 24
 
kikou
0
Darnc
 
Habitual (for a long time to 2000 & 2003 ...yes I know it's a pleonasm) the trick in all versions is quite nice.
0