Excel中特定行的最后一列 [英] Last column of a specific row in Excel

问题描述

我必须找到工作表中一行的最后一列.我能够找到工作表中的最后一列，但是对于特定的行，我需要找到最后一列，这对于excel中的每个工作表都会有所不同，并且在每次运行时都会有所不同.为了找到最后一列，我使用了以下代码，并参考了问题

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

已更新:尝试使用以下代码，但显示错误代码为91.函数为:

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

在代码中将其调用为:

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

推荐答案

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

只需将 i 替换为行号.

这篇关于Excel中特定行的最后一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！