Last column vba

[whatsup]

I have data in 4 rows and 5 columns.

Sub test()

Dim lr, lc As Long

1) lr = Range("a" & Rows.Count).End(xlUp).Row - This works fine and i get lr = 4

2) lc = Range("a" & Columns.Count).End(xlToLeft).Column ( lc = 1, wonder why this is the case, shouldnt it be 5?)

3) lc = Cells(1, Columns.Count).End(xlToLeft).Column ( this works giving me lc = 5 but iam wondering why number 2 code above gives me lc = 1

end sub

 

[Joe4]

2) lc = Range("a" & Columns.Count).End(xlToLeft).Column ( lc = 1, wonder why this is the case, shouldnt it be 5?)

You are a bit confused here. You have two column references "A" and "Columns.Count" and no row reference anywhere!

The way you did it in item 3 is the correct way to find the last column in row 1 with data.

 

[Marc L]

​

And 'LastRow' & 'LastColumn' can be easily avoided - often useless - just using the property CurrentRegion …​

 

[Joe4]

​

And 'LastRow' & 'LastColumn' can be easily avoided - often useless - just using the property CurrentRegion …​

That might not always work if you have gaps in the middle of your data (completely blank rows or columns in the data).
CurrentRegion only works on Contiguous data.

 

[Fluff]

That assumes that there are no blank columns or rows within the data.

 

[whatsup]

You are a bit confused here. You have two column references "A" and "Columns.Count" and no row reference anywhere!

The way you did it in item 3 is the correct way to find the last column in row 1 with data.

Why would i need a row reference, when i only want to find the last column?

So if specify row, that is A1

lc = Range("a1" & Columns.Count).End(xlToLeft).Column

Also doesn't work, gives me last column is 1

Not able to clearly understand

 

[alansidman]

For last column, I use the following:

lc= cells(1,columns.count).end(xltoleft).column

which looks for the last column based upon row 1 which usually holds your field names.

My 2 cents.

 

[Joe4]

Why would i need a row reference, when i only want to find the last column?

Because you need to construct a valid range reference that accurately reflects what you want.

If you wanted to use "Range" and only have a column reference, it would need to look something like this:

Range("E:F").Select

But look what you are building:

Columns.Count returns 16384.
So this:

lc = Range("a" & Columns.Count).End(xlToLeft).Column

would translate to this:

lc = Range("A16384").End(xlToLeft).Column

which is actually column A, row 16384.
Since you are already starting in column A, you cannot move any further to the left, so this will ALWAYS return column 1.

So if specify row, that is A1

lc = Range("a1" & Columns.Count).End(xlToLeft).Column

Also doesn't work, gives me last column is 1

Not able to clearly understand

Once again, use like the previous one, Columns.Count will return 16384.
So by concatenating on "A1" in front of that reference, you now have:

lc = Range("A116384").End(xlToLeft).Column

which is column A, row 116384.
And just like the previous explanation, since you are starting in column A, you cannot move any further to the left and will always return column 1.

When you cannot figure things like this out, the best thing to do is start subtituting the values like I showed above to see what you are really building.

Also note when using "Range" in referencing ranges, the column reference HAS to be a letter, it cannot be a number. That is very limiting, and makes it hard to get the last column reference using this method.
However, using "Cells", you can refer to the column by the letter or by the numeric reference (i.e. "A"=1, "B"=2, "C"=3,...).
This gives you much more flexibility, and allows you to find the last column with data in a row, using the method show in your 3rd item in your original post.

Hope that clarifies things for you.