Returning last columns with no values in (but possibly w. formulas

[Kragelund]

I picked up the following code from a previous thread. The code selects the
last column that has formulas in it. How do I modify the code to select the
last column, which has no values in it (but may still have code in that
returns ""). I need it for dimensioning the size of a print area, which
should obviously not include empty cells.

Thanks!

Kragelund


Public Sub test()
Cells(1, LastColumn()).Select
End Sub

Public Function LastColumn(Optional wks As Worksheet) As Integer
If wks Is Nothing Then Set wks = ActiveSheet
LastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function

 

[excelent]

=MAX(IF(1:1<>"",COLUMN(1:1))+1)

insert and type CTRL+SHIFT+ENTER

return last column in row 1

"Kragelund" skrev:

 

[Gary''s Student]

This might be what you want:

Function lastcolumn()
Dim r As Range
For i = 256 To 1 Step -1
Set r = Range(Cells(1, i), Cells(65536, i))
If Application.WorksheetFunction.CountBlank(r) <> 65536 Then
Exit For
End If
Next
lastcolumn = i + 1
End Function


It start with the last column and works towards the beginning. When it
finds a column with values in it, it knows it went too far. The column is
adjusted "back" by one.


The function does return a column containing only blanks or formulae
returning blanks.

 

[Kragelund]

It is indeed, Gary's student tnanks!

Kragelund


"Gary''s Student" skrev:

 

[Kragelund]

Thanks Excelent, I want to do this in VBA though, but your solution is
elegant on the application side, I'll keep it in mind for another occasion.

Kragelund

"excelent" skrev: