51

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?

jamheadart
  • 5,047
  • 4
  • 32
  • 63
Neat Machine
  • 681
  • 4
  • 11
  • 18
  • 2
    This might not work for Excel 2007+ workbook, which may have over a million rows. You might want to use [`ws.UsedRange`](http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.usedrange(v=vs.80).aspx) instead. – ikh Aug 13 '12 at 01:24
  • Ah, thanks for that. I didn't know. – Neat Machine Aug 13 '12 at 02:14
  • 3
    @ikh, its prob better to use `ws.Cells(Rows.Count, "A").End(xlUp).row` – Reafidy Aug 13 '12 at 02:16
  • 2
    possible duplicate of [Copy last column with data on specified row to the next blank column](http://stackoverflow.com/questions/11883256/copy-last-column-with-data-on-specified-row-to-the-next-blank-column) – Siddharth Rout Aug 13 '12 at 03:43

7 Answers7

95

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.

Community
  • 1
  • 1
Reafidy
  • 8,240
  • 5
  • 51
  • 83
  • 1
    + 1 readfidy :) A suggestion though... Always use `Application.CountA` with `.Find` to avoid errors. See this http://stackoverflow.com/questions/11883256/copy-last-column-with-data-on-specified-row-to-the-next-blank-column/11883425#11883425 – Siddharth Rout Aug 13 '12 at 03:39
  • 1
    Thanks @Sidd, you will find exactly that suggestion and additional info in the link I provided to Ozgrid in my comment above. – Reafidy Aug 13 '12 at 04:15
  • True :) I should have visited that link :) – Siddharth Rout Aug 13 '12 at 04:17
  • 1
    @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. – Reafidy Aug 13 '12 at 04:21
  • @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 :) – brettdj Aug 13 '12 at 05:27
  • @brettdj: Yup that also be can be used but if you are storing the lastcol in a variable then we can use the worksheet function as well. The other option would be to declare a range and then find the column if applicable. :) – Siddharth Rout Aug 13 '12 at 05:48
  • @brettdj: I have added your suggestion to the link http://stackoverflow.com/questions/11883256/copy-last-column-with-data-on-specified-row-to-the-next-blank-column/11883425#11883425 – Siddharth Rout Aug 13 '12 at 05:54
  • Just make sure the filters are always clear, or this may not always give an accurate result. `If ws.FilterMode Then ws.ShowAllData`. – Andy Dec 19 '20 at 08:24
20

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
xn1
  • 417
  • 4
  • 12
2

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.

Rob
  • 4,927
  • 12
  • 49
  • 54
Peter
  • 21
  • 1
2

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.

Community
  • 1
  • 1
Stupid_Intern
  • 3,382
  • 8
  • 37
  • 74
1

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)
dapaz
  • 813
  • 10
  • 16
  • 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 https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Jun 28 '18 at 10:14
  • 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 – dapaz Jun 29 '18 at 07:50
0

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
FreeSoftwareServers
  • 2,271
  • 1
  • 33
  • 57
0

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.

Alex Kwitny
  • 11,211
  • 2
  • 49
  • 71