By Neat Machine

2012-08-13 00:33:45 8 Comments

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


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


@dapaz 2018-06-27 09:09:47

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 2018-06-28 10:14:43

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…

@dapaz 2018-06-29 07:50:51

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

@xn1 2016-04-19 11:52:00

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

@Stupid_Intern 2016-03-11 16:36:35

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.

@Peter 2013-05-22 06:55:31

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 2019-07-16 07:44:05

If you use this method with Column, it gives a wrong column.

@Reafidy 2012-08-13 02:00:54

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.

@Siddharth Rout 2012-08-13 03:39:57

+ 1 readfidy :) A suggestion though... Always use Application.CountA with .Find to avoid errors. See this…

@Reafidy 2012-08-13 04:15:48

Thanks @Sidd, you will find exactly that suggestion and additional info in the link I provided to Ozgrid in my comment above.

@Siddharth Rout 2012-08-13 04:17:08

True :) I should have visited that link :)

@Reafidy 2012-08-13 04:21:20

@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 2012-08-13 05:27:27

@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 :)

@Siddharth Rout 2012-08-13 05:48:09

@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 2012-08-13 05:54:47

@brettdj: I have added your suggestion to the link…

Related Questions

Sponsored Content

44 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1114232 View
  • 1912 Score
  • 44 Answer
  • Tags:   c# .net excel file-io

13 Answered Questions

[SOLVED] How can I find last row that contains data in a specific column?

  • 2008-09-16 10:54:53
  • Lipis
  • 351936 View
  • 64 Score
  • 13 Answer
  • Tags:   excel vba

1 Answered Questions

macro help to find LastRow IF

  • 2019-05-08 19:10:48
  • Mancho
  • 48 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] Finding last column and then finding the last row in that column

2 Answered Questions

[SOLVED] How to find the last cell in a column which is supposed to be blank but has spaces?

  • 2015-09-26 09:21:01
  • emeraldgreen
  • 389 View
  • 1 Score
  • 2 Answer
  • Tags:   vba excel

1 Answered Questions

[SOLVED] Finding The Last Column With Filled with Data in a Range

  • 2013-12-28 09:12:39
  • Behseini
  • 22446 View
  • 0 Score
  • 1 Answer
  • Tags:   vba excel

1 Answered Questions

[SOLVED] Find last element in column

  • 2014-08-07 09:26:54
  • KOM
  • 3089 View
  • 0 Score
  • 1 Answer
  • Tags:   excel vba

2 Answered Questions

[SOLVED] first Occurrence Of Non Blank Cell vba

  • 2010-06-03 00:09:27
  • BumbleBee
  • 18229 View
  • 0 Score
  • 2 Answer
  • Tags:   excel vba

Sponsored Content