How to find the last column which is not empty

moramramesh

New Member
Joined
May 22, 2019
Messages
17
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)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
You can find the last populated column in row 1 like this:
Code:
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.
Code:
Cells(1, lc).Activate
 
Last edited:
Upvote 0
Try:
Code:
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
 
Upvote 0
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.
 
Upvote 0
Try:
Rich (BB code):
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.
 
Upvote 0
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0
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)
 
Upvote 0
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:
Code:
[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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top