Last column with data in table - VBA or not

[limalf]

Hi,

I have a table object (in the range A1:M20) with some numbers spread around. I have been using the following code to find the last column, but it always show the last column of the table, event if there is not data (numbers) on the last column. Even if I have numbers only until column B, LC is always 13.

LC=Sheet.Cells(x, Columns.Count).End(xlToLeft).Column

Can someone help me to find the way to show the column index of the actual last column with data?

(Sorry, I was not able to wrap the code.)

 

[Peter_SSs]

Welcome to the MrExcel board!

Does this do what you want?

LC = Range("A2:M20").Find(What:="*", After:=Range("A2"), LookIn:=xlValues, _
  SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column

 

[hiker95]

limalf,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


The following will find the last used column in the active worksheet.

Sub FindLastUsedColumn()
' hiker95, 10/21/2014, ME813020
Dim lc As Long
lc = ActiveSheet.Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
End Sub

 

[limalf]

Thank you both for your reply, but I was not totally clear in my previous post.

I have a routine with to generate a database from a table (matrix). So I want to find the column number of the last cell with data in each row (x). As mentioned the code LC=Sheet.cells(x, Columns.Count).End(xltoLeft).Column, indicates me the last column of the table.

Can you help me to adapt your code to what i need?

**************
Windows 7 pro
MSOffice 2010

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=IFERROR(LARGE(IF(ISNUMBER(A2:M20),COLUMN(A2:M20)-COLUMN(A2)+1),1),"No numeric data")

would yield the relative column number of the last cell housing a number.

 

[Peter_SSs]

Control+shift+enter, not just enter:

=IFERROR(LARGE(IF(ISNUMBER(A2:M20),COLUMN(A2:M20)-COLUMN(A2)+1),1),"No numeric data")

would yield the relative column number of the last cell housing a number.

Something like this then?
A zero represents an empty row.

Sub Last_Cols()
  Dim rw As Range
  Dim LastColList As String
  Dim LC As Long

  For Each rw In Range("Table1").Rows
    LC = 0
    On Error Resume Next
    LC = rw.Find(What:="*", After:=rw.Cells(1), LookIn:=xlValues, SearchDirection:=xlPrevious, SearchFormat:=False).Column
    On Error GoTo 0
    LastColList = LastColList & vbLf & LC
  Next rw
  MsgBox "Last columns:" & LastColList
End Sub