Returning last columns with no values in (but possibly w. formulas
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 |
Returning last columns with no values in (but possibly w. formulas
=MAX(IF(1:1<"",COLUMN(1:1))+1)
insert and type CTRL+SHIFT+ENTER return last column in row 1 "Kragelund" skrev: 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 |
Returning last columns with no values in (but possibly w. formulas
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. -- Gary''s Student gsnu200703 "Kragelund" wrote: 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 |
Returning last columns with no values in (but possibly w. form
It is indeed, Gary's student tnanks!
Kragelund "Gary''s Student" skrev: 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. -- Gary''s Student gsnu200703 "Kragelund" wrote: 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 |
Returning last columns with no values in (but possibly w. form
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: =MAX(IF(1:1<"",COLUMN(1:1))+1) insert and type CTRL+SHIFT+ENTER return last column in row 1 "Kragelund" skrev: 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 |
All times are GMT +1. The time now is 07:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com