Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Matching cells across columns, and returning equal values | Excel Discussion (Misc queries) | |||
vlookups returning formulas - not values - in excel | Excel Worksheet Functions | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming | |||
vlookup formulas returning no values | Excel Worksheet Functions | |||
Formulas returning errors when reference columns are deleted | Excel Programming |