How do I find the last column with data?

Question

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?

• ikh: This might not work for Excel 2007+ workbook, which may have over a million rows. You might want to use ws.UsedRange instead.
• Neat Machine: Ah, thanks for that. I didn't know.
• Reafidy: @ikh, its prob better to use ws.Cells(Rows.Count, "A").End(xlUp).row
• Siddharth Rout: possible duplicate of Copy last column with data on specified row to the next blank column

Answer 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, ws.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.

• Siddharth Rout: + 1 readfidy :) A suggestion though... Always use Application.CountA with .Find to avoid errors. See this stackoverflow.com/questions/11883256/…
• Reafidy: Thanks @Sidd, you will find exactly that suggestion and additional info in the link I provided to Ozgrid in my comment above.
• Siddharth Rout: True :) I should have visited that link :)
• Reafidy: @Sidd, it was still a good point though, and worth mentioning. I just prefer not to post full answers with all error handling etc, I consider that to be doing there work for them, and in my interpretation that's outside the scope of this forum.
• brettdj: @SiddharthRout Given there is a range object to work with testing for Not Nothing is a little cleaner than invoking a worksheet function. +1 btw Reafidy :)

Answer 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

Answer 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.

• Stefan Steiger: If you use this method with Column, it gives a wrong column.

Answer 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

enter image description here

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

Above line Yields 9 in the immediate window.

Answer 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)

• Luuklag: What makes your answer different then the accepted answer, besides being shorter? Also please avoid the use of the Select statement. That is really bad practice. See stackoverflow.com/questions/10714251/…
• dapaz: it's short and simple and that's the value added. thanks for the tip on the Select statement. I will have a look. Cheers

Answer 6

I have been using @Reafidy method/answer for a long time, but today I ran into an issue with the top row being merged cell from A1-->N1 and my function returning the "Last Column" as 1 not 14.

Here is my modified function now account for possibly merged cells:

Public Function Get_lRow(WS As Worksheet) As Integer
 On Error Resume Next
 If Not IsWorksheetEmpty(WS) Then
 Get_lRow = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 Dim Cell As Range
 For Each Cell In WS.UsedRange
 If Cell.MergeCells Then
 With Cell.MergeArea
 If .Cells(.Cells.Count).Row > Get_lRow Then Get_lRow = .Cells(.Cells.Count).Row
 End With
 End If
 Next Cell
 Else
 Get_lRow = 1
 End If
End Function

---

Public Function Get_lCol(WS As Worksheet) As Integer
 On Error Resume Next
 If Not IsWorksheetEmpty(WS) Then
 Get_lCol = WS.Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 Dim Cell As Range
 For Each Cell In WS.UsedRange
 If Cell.MergeCells Then
 With Cell.MergeArea
 If .Cells(.Cells.Count).Column > Get_lCol Then Get_lCol = .Cells(.Cells.Count).Column
 End With
 End If
 Next Cell
 Else
 Get_lCol = 1
 End If
End Function

Answer 7

Here's a simple option if your data starts in the first row.

MsgBox "Last Row: " + CStr(Application.WorksheetFunction.CountA(ActiveSheet.Cells(1).EntireRow))

It just uses CountA to count the number of columns with data in the entire row.

This has all sorts of scenarios where it won't work, such as if you have multiple tables sharing the top row, but for a few quick & easy things it works perfect.