Last column of a specific row in Excel

Question

I have to find the last column of a row in a sheet. I am able to find the last column in the sheet, but for a particular row, I need to find the last column which will vary for every sheet in the excel, and it will vary at every run. To find the last column, I have used the below code, with reference from the question Finding last column across multiple sheets in a function:

For Each ws In ThisWorkbook.Sheets
 lc = ws.Cells.Find("*", SearchOrder:=xlByColumns,      
 SearchDirection:=xlPrevious).Column
 Debug.Print ws.Name, lc
 MsgBox lc
 Next ws

Updated: Trying to use the below code, but its showing error code 91. Function is :

 Function lastColumn(Optional sheetName As String, Optional 
rowToCheck  As Long =  1) As Long

Dim ws  As Worksheet

If sheetName = vbNullString Then
    Set ws = ActiveSheet
Else
    Set ws = Worksheets(sheetName)
End If

lastColumn = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

Calling it in the code as:

For Each ws In ThisWorkbook.Worksheets

    i = ws.Columns(2).Find("Total").Row (error code as 91)
    Debug.Print lastColumn(ws.Name, i)
Next ws

Use ws.Cells(i, ws.Columns.Count).End(xlToLeft).Column where i = the number of the row. — dwirony
– dwirony, Commented Mar 22, 2018 at 15:13
Error 91 means it didn't find the word "Total" in column B of the sheet you checked. Are certain every single sheet has a cell with "Total" in it somewhere in column B? You may need to add a check to make sure it's being found properly. — tigeravatar
– tigeravatar, Commented Mar 22, 2018 at 19:27

dwirony · Accepted Answer · 2018-03-22 15:14:59Z

2

Sub Test()

For Each ws In ThisWorkbook.Sheets
    lc = ws.Cells(i, ws.Columns.Count).End(xlToLeft).Column
    Debug.Print ws.Name, lc
    MsgBox lc
Next ws

End Sub

Just replace i with the row number.

answered Mar 22, 2018 at 15:14

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vityata · Accepted Answer · 2018-03-22 16:31:15Z

1

This is the function that I am using for lastColumn per specific row:

Function lastColumn(Optional sheetName As String, Optional rowToCheck As Long = 1) As Long

    Dim ws  As Worksheet

    If sheetName = vbNullString Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(sheetName)
    End If

    lastColumn = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column

End Function

It takes optional arguments sheetName and rowToCheck. This is a way to run it for your case:

Public Sub TestMe()

    Dim ws      As Worksheet
    Dim lc      As Long
    lc  = 8

    For Each ws In ThisWorkbook.Worksheets
        Debug.Print lastColumn(ws.Name, lc)
    Next ws

End Sub

edited Mar 22, 2018 at 16:31

answered Mar 22, 2018 at 15:20

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

7 Comments

user9351236 Over a year ago

This gives the last column number in the sheet, but I need the last column number of a line. This I have already tried, but each sheet has a different row number whose last column number has to be found out.

Vityata Over a year ago

@user9351236 - what is a line? A row?

Vityata Over a year ago

@user9351236 - and based on what it will vary?

user9351236 Over a year ago

The data is different in each sheet...so the count of the row number will be different for each sheet..

Vityata Over a year ago

@user9351236 - so you are looking for the last column of the last row per sheet?

|

Ionut · Accepted Answer · 2018-03-22 15:15:42Z

0

Try this :

With Worksheets(set_sheet_name)
    LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Column
End With

this will get you nr. of columns from the line "5", if you want another line just change the 5 with whatever line you need.

answered Mar 22, 2018 at 15:15

Ionut

911 silver badge10 bronze badges

Collectives™ on Stack Overflow

Last column of a specific row in Excel

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related