How to find the last column which is not empty

[moramramesh]

I have the below code:

Range("A:A,B:B,C:C,D:D,E:E,F:F,G:G,H:H,I:I").Select
Range("I1").Activate
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("Sheet2!$A1:$I$" & lr2)


Since I know that data is available from columns A to I, I have passed all the columns in the range function. But I would like to pass range value dynamically based on the last column containing data.
Accordingly in the 2nd line, I want pass the last column first cell instead of Range("I1").Activate
Similarly in the 4th line, I want to pass dynamic column value in place of I, instead of Range("Sheet2!$A1:$I$" & lr2)

 

[Joe4]

You can find the last populated column in row 1 like this:

Dim lc as Long
lc = Cells(1, Columns.Count).End(xlToLeft).Column

Note that this returns a column number, not a column letter. So you would refer to ranges uses Cells instead of Ranges, i.e.

Cells(1, lc).Activate

 

[JoeMo]

Try:

Sub LastColumn()
Dim lastCol As Long
Range("A:I").Select
lastCol = ActiveSheet.Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
Cells(1, lastCol).Activate
End Sub

 

[bbotzong]

Have you tried selecting a cell in the table, then using Selection.CurrentRegion.Select? That will highlight all the data. I believe you can pass a range variable into your last line. Here's what I did:

dim Rng as Range
Range("A1").select
set rng=selection.CurrentRegion

ActiveSheet.shapes.AddChart2(227, xlLine).select
ActiveChart.SetSourceData source:=Rng

Hope that helps.

 

[Rick Rothstein]

Try:

Sub LastColumn()
Dim lastCol As Long
Range("A:I").Select
lastCol = ActiveSheet.Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column
Cells(1, lastCol).Activate
End Sub

If you want to save a little typing, you can omit the argument I highlighted in red... when the SearchDirection is xlPrevious, the Find function automatically uses the first cell of the specified range.

 

[JoeMo]

If you want to save a little typing, you can omit the argument I highlighted in red... when the SearchDirection is xlPrevious, the Find function automatically uses the first cell of the specified range.

I generally try to specify all the key parameters when using the Find Method as changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments.

 

[Rick Rothstein]

I generally try to specify all the key parameters when using the Find Method as changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments.

The "After" argument for the Find method is not one of the "memorized" options probably because setting it is not available in the Find (or Replace) dialog box (but I do understand your desire for completeness when setting the arguments).

 

[moramramesh]

I'm able to get the Column Letter using the below code:
'Finding the last column
lc = Cells(1, Columns.Count).End(xlToLeft).Column
'Convert To Column Letter
ColumnLetter = Split(Cells(1, lc).Address, "$")(1)

But when trying to pass the ColumnLetter to Range as below, I'm getting the Run time error: Method 'Range' of object '_Global' failed
Range("A:ColumnLetter").Select
Range(ColumnLetter).Activate
ActiveSheet.Shapes.AddChart2(227, xlLine).Select
ActiveChart.SetSourceData Source:=Range("SORT!$A1:$ColumnLetter$" & lr2)

 

[Joe4]

It is important to understand the difference between variables and literal values.
Everything enclosed in double-quotes is treated as literal values. Variables must be OUTSIDE of the double quotes.
You can concatenate literal text and values returned by variables toegether with "&".

So your last line should look like:

[COLOR=#333333]ActiveChart.SetSourceData Source:=Range([/COLOR][COLOR=#0000ff]"SORT!$A1:$"[/COLOR][COLOR=#333333] & [/COLOR][COLOR=#ff0000]ColumnLetter[/COLOR][COLOR=#333333] & [/COLOR][COLOR=#0000ff]  "$" [/COLOR] [COLOR=#333333] & [/COLOR][COLOR=#ff0000]lr2[/COLOR][COLOR=#333333])[/COLOR]

I made the literal text blue and the variables red to highlight this point.
I assume that you are calculating lr2 elsewhere, or else your code will fail (as lr2 would be 0 if it is not being set anywhere and there is no row zero).

 

[moramramesh]

I'm unable to pass the variable here: Range(ColumnLetter).Activate. I tried with &, "", etc but getting error. Hence I used Cells(1, lc).Activate instead of Range. However I would like to know how we can pass variable for Range function having single value.
For other lines I was able to pass the variable by concatenating but since the below line contains only one value, I'm not getting it how to pass the variable.

Range("I1").Activate '-----> Need to pass variable "ColumnLetter" instead of I1.