Excel VBA- Finding the last column with data

问题:

I\'ve found this method for finding the last data containing row in a sheet:

ws.Range(\"A65536\").End(xlUp).row

Is there a similar method for finding the last data containing column in a sheet?

Thanks.

回答1:

Lots of ways to do this. The most reliable is find.

Dim rLastCell As Range

Set rLastCell = ws.Cells.Find(What:=\"*\", After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

MsgBox (\"The last used column is: \" & rLastCell.Column)

If you want to find the last column used in a particular row you can use:

Dim lColumn As Long

lColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column

Using used range (less reliable):

Dim lColumn As Long

lColumn = ws.UsedRange.Columns.Count

Using used range wont work if you have no data in column A. See here for another issue with used range:

See Here regarding resetting used range.

回答2:

I know this is old, but I\'ve tested this in many ways and it hasn\'t let me down yet, unless someone can tell me otherwise.

Row number

Row = ws.Cells.Find(What:=\"*\", After:=[A1] , SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Column Letter

ColumnLetter = Split(ws.Cells.Find(What:=\"*\", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Cells.Address(1, 0), \"$\")(0)

Column Number

ColumnNumber = ws.Cells.Find(What:=\"*\", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

回答3:

Try using the code after you active the sheet:

Dim J as integer
J = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

If you use Cells.SpecialCells(xlCellTypeLastCell).Row only, the problem will be that the xlCellTypeLastCell information will not be updated unless one do a \"Save file\" action. But use UsedRange will always update the information in realtime.

回答4:

I think we can modify the UsedRange code from @Readify\'s answer above to get the last used column even if the starting columns are blank or not.

So this lColumn = ws.UsedRange.Columns.Count modified to

this lColumn = ws.UsedRange.Column + ws.UsedRange.Columns.Count - 1 will give reliable results always

?Sheet1.UsedRange.Column + Sheet1.UsedRange.Columns.Count - 1

Above line Yields 9 in the immediate window.

回答5:

Here\'s something which might be useful. Selecting the entire column based on a row containing data, in this case i am using 5th row:

Dim lColumn As Long

lColumn = ActiveSheet.Cells(5, Columns.Count).End(xlToLeft).Column
MsgBox (\"The last used column is: \" & lColumn)