Finding Column # of the Last Filled Column

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Finding Column # of the Last Filled Column

Post by kpark91 »

Hello,

I'm sure this is an easy one but I'm having a brain fart.

On a different worksheet, I have the following code.

Code: Select all

Const Original = "Hello"
Const Final = "No"


Sub TEST()
    Dim LR_Original, LR_Final As Integer
    
    LR_Original = Workbooks(Original).Worksheets("Hello1").Cells(Columns.Count, 1).End(xlToLeft).Column
    
End Sub
When I debug it, it seems to be unable to get a value of the last column.
I think I'm doing something wrong but not sure what... :groan:

Thank you :D
I don't have one

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Column # of the Last Filled Column

Post by HansV »

First a peculiarity of Visual Basic: the declaration

Dim LR_Original, LR_Final As Integer

defines only LR_Final as an Integer; no data type is specified for LR_Original and hence it is defined as a Variant. If you want both to be Integers, you must use

Dim LR_Original As Integer, LR_Final As Integer

The line

LR_Original = Workbooks(Original).Worksheets("Hello1").Cells(Columns.Count, 1).End(xlToLeft).Column

is dangerous if Original is not the active workbook. Because you don't specify what Columns belongs to, it is assumed to be the Columns collection of the active worksheet in the active workbook. If for example the active workbook is an Excel 2007/2010 workbook and Original an Excel 97-2003 workbook, the number of columns will not be the same.

Moreover, it is incorrect: in Cells, you must first specify the row, then the column. So to get the column number of the last filled column in row 1, you should use

LR_Original = Workbooks(Original).Worksheets("Hello1").Cells(1, Workbooks(Original).Worksheets("Hello1").Columns.Count).End(xlToLeft).Column

If you want the column number of the last filled column overall (regardless of row), you need a different expression.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Finding Column # of the Last Filled Column

Post by HansV »

Best wishes,
Hans

kpark91
StarLounger
Posts: 61
Joined: 29 Jul 2010, 14:52

Re: Finding Column # of the Last Filled Column

Post by kpark91 »

Wow. I never realized I had to write As [DataType] everytime I declare a variable D:
I thought it was alright for the last 3 months!!

The code works perfectly now :)

Thank you very much.
Carbohydrates for the brain :D :chocciebar:
I don't have one