Last used column

[VBABEGINER]

I need "Last used column" in code. Example, last column used is AI, so AI column number in i variable

 

[My Aswer Is This]

Re: Lat used column

If your looking for last used column in Row(1)
Try this:

Sub Lastcolum_Me()
Dim Lastcolumn As Long
Lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox Lastcolumn
End Sub

 

[Joe4]

Re: Lat used column

Perhaps:

    Dim i As Long
    i = Range("A1").SpecialCells(xlLastCell).Column

Note: Don't be thrown by the "Range("A1")" reference. You could put any cell there (including "ActiveCell").
This formula is not subject to any specific row, but rather the whole worksheet.

 

[MickG]

Re: Lat used column

Perhaps:-!!
This code is for last column "Selected" , use "ChangeEvent for last column "Changed".

Option Explicit
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
 [COLOR=navy]Dim[/COLOR] i [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
    [COLOR=navy]If[/COLOR] Rng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
        [COLOR=navy]Set[/COLOR] Rng = Target
    [COLOR=navy]Else[/COLOR]
        i = Rng.Column
        MsgBox i
        [COLOR=navy]Set[/COLOR] Rng = Target
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[hiker95]

Re: Lat used column

VBABEGINER,

See if the following will help you.

Sub LastUsedColumn()
' hiker95, 04/19/2018, ME1052368

Dim lastcolumn As Long

lastcolumn = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column

End Sub

 

[Fluff]

Re: Lat used column

Yet another way

Sub LastCol()
   Dim UsdCols As Long
   UsdCols = ActiveSheet.UsedRange.Columns.Count
End Sub

 

[VBABEGINER]

Re: Lat used column

All,
Fluff, hiker, MikeG, Joe and "* Answer *"...Thanks guys..Appreciate your reply..

 

[VBABEGINER]

Re: Lat used column

Can I seek one more help..I am giving explanation here first..

I have 7-8 sheets. Region wise.
Col A
Col B = Emp id (Mannual entered)
Col C = Emp Names (vlookup)
Col D = process names (vlookup)
.
.
.
and so on till last col.. (vlookup)
'*****************************

With every sheet I keep 40 rows like this to add employee names.
Max employee names are 14 on sheet 5 East region...employee may increase/decrease..
'*****************************

what im trying to do..copy first region data only till last employee name on that particular sheet and paste onto Sheet name "Combine"

Go to next sheet, copy that again till employee name last and paste it onto next row record of "Combine"

how do i achieve this..
I will share the code till now..

Sub fnCombine()
Dim n As Integer


'To always select 1st sheet
n = ActiveWorkbook.Worksheets.Count
ActiveWorkbook.Worksheets("Atlantic").Select


i = Range("A1:A5000").Find("Monthly", Range("A1"), xlValues, xlWhole, xlByColumns, xlNext).Row
LC = Cells(3, Columns.Count).End(xlToLeft).Column


Range(Cells(i + 4, 1), Cells(?, LC)).Select


'? = last employee row
End Sub

Edit Part..
Employee Names are in Col C.
Employee Id which are in Col B (Manual Entry) are contains "0" zero.

that means, for 1st region sheet, employee id last is say..12345 then I kept next records till 40 row's as 0.


'*********
Apologies my post goes wrong about names...hold on

 

[VBABEGINER]

Re: Lat used column

can i please get help on this..

bLR = Range("B5").End(xlDown).Count

it giving me row number i.e. 13. I dont want that 13..I want count..So i will understand here only how employee names are there..

 

[VBABEGINER]

Re: Lat used column

can i get any suggestion on this..

can i please get help on this..

bLR = Range("B5").End(xlDown).Count

it giving me row number i.e. 13. I dont want that 13..I want count..So i will understand here only how employee names are there..