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

@Prison Mike 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.

@Neat Machine 2012-08-13 02:14:09

Perfect. Thank you.

@Reafidy 2012-08-13 02:15:29

You welcome, more info here

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

@Siddharth Rout 2012-08-13 04:26:36

Yup you have a valid point.

@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

13 Answered Questions

[SOLVED] How to avoid using Select in Excel VBA

  • 2012-05-23 05:57:58
  • 251050 View
  • 501 Score
  • 13 Answer
  • Tags:   excel vba

22 Answered Questions

[SOLVED] Is there a way to crack the password on an Excel VBA Project?

  • 2009-06-22 10:37:13
  • Jonathan Sayce
  • 1053180 View
  • 456 Score
  • 22 Answer
  • Tags:   excel vba passwords

42 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
  • 1028761 View
  • 1779 Score
  • 42 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
  • 339149 View
  • 59 Score
  • 13 Answer
  • Tags:   excel vba

7 Answered Questions

[SOLVED] How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

  • 2014-03-20 19:09:13
  • Portland Runner
  • 787894 View
  • 531 Score
  • 7 Answer
  • Tags:   regex excel vba

2 Answered Questions

[SOLVED] Excel VBA - Finding Last Non-Empty Row Based on Column Header

3 Answered Questions

[SOLVED] Shortcut to Apply a Formula to an Entire Column in Excel

  • 2011-03-24 20:37:34
  • John Shedletsky
  • 891425 View
  • 305 Score
  • 3 Answer
  • Tags:   excel excel-formula

3 Answered Questions

[SOLVED] Excel / VBA last row/column

  • 2013-05-03 13:04:43
  • were_cat
  • 6288 View
  • 1 Score
  • 3 Answer
  • Tags:   vba excel-vba excel

1 Answered Questions

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

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

2 Answered Questions

[SOLVED] first Occurrence Of Non Blank Cell vba

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

Sponsored Content