|
Find last used column
I am using the following to try to identify the last used column and then
select the top cell of the first empty column after the last used: myRange = ActiveSheet.UsedRange NewColumn = (myRange.Columns(myRange.Columns.Count).Column) + 1 ActiveSheet.Cells(1, NewColumn).Select Can anyone tell me what I am doing wrong? This is not working. The portion in () in the second line I copied from the help file, but do not understand. -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 |
Find last used column
By last column I assume you mean the last column with a value in it
anywhere??? Try this... Public Sub test() Cells(1, LastColumn() + 1).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 -- HTH... Jim Thomlinson "Bryan Brassell" wrote: I am using the following to try to identify the last used column and then select the top cell of the first empty column after the last used: myRange = ActiveSheet.UsedRange NewColumn = (myRange.Columns(myRange.Columns.Count).Column) + 1 ActiveSheet.Cells(1, NewColumn).Select Can anyone tell me what I am doing wrong? This is not working. The portion in () in the second line I copied from the help file, but do not understand. -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 |
Find last used column
It's kind of unnecessary.
myRange=ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1,myRange +1).Select Works for me. Jesse |
Find last used column
Really what I need is the first blank column on a particular worksheet. I
need to paste in a colum of numbers representing completed work. The next time a batch is run, I need to paste that set of numbers on the same sheet, in the next column, and so on... -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 "Jim Thomlinson" wrote: By last column I assume you mean the last column with a value in it anywhere??? Try this... Public Sub test() Cells(1, LastColumn() + 1).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 -- HTH... Jim Thomlinson "Bryan Brassell" wrote: I am using the following to try to identify the last used column and then select the top cell of the first empty column after the last used: myRange = ActiveSheet.UsedRange NewColumn = (myRange.Columns(myRange.Columns.Count).Column) + 1 ActiveSheet.Cells(1, NewColumn).Select Can anyone tell me what I am doing wrong? This is not working. The portion in () in the second line I copied from the help file, but do not understand. -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 |
Find last used column
myRange=Sheets(DestinationSheet).UsedRange.Columns .Count ActiveSheet.Cells(sourcedata).EntireColumn.Copy Destination:=Sheets(DestinationSheet).Cells(1,myRa nge +1).EntireColumn Jesse |
Find last used column
Bryan,
myRange is an object variable. You tell Excel what it refers to by using a "Set" statement. Other types of variables do not require this step. Change... myRange =... to Set myRange =... I remember seeing that line code in the help file for the first time and wondering how it worked. It turns out that it is a very useful technique. "Columns" is a collection, so Range.Columns returns all the columns in a range.(an object) Range.Columns(1) returns the first column in the range. (an object) Range.Columns.Count tells you how many columns there are in the range. ( a long) Substituting the count of the columns inside the parenthesis returns the last column in the range. (an object) Adding the .Column to the end returns the column number. -- Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Bryan Brassell" wrote in message ... I am using the following to try to identify the last used column and then select the top cell of the first empty column after the last used: myRange = ActiveSheet.UsedRange NewColumn = (myRange.Columns(myRange.Columns.Count).Column) + 1 ActiveSheet.Cells(1, NewColumn).Select Can anyone tell me what I am doing wrong? This is not working. The portion in () in the second line I copied from the help file, but do not understand. -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 |
Find last used column
Careful with they used range. It is not always what you might think it is. It
is at least the size of the populated data, but if you have saved the file then deleted data and not re-saved then it may be larger than you think. -- HTH... Jim Thomlinson "Jesse" wrote: It's kind of unnecessary. myRange=ActiveSheet.UsedRange.Columns.Count ActiveSheet.Cells(1,myRange +1).Select Works for me. Jesse |
Find last used column
myRange=Sheets(DestinationSheet).UsedRange.Columns .Count
ActiveSheet.Cells(sourcedata).EntireColumn.Copy _ Destination:=Sheets(DestinationSheet).Cells(1,myRa nge +1).EntireColumn Sorry, auto word wrap. Jesse |
Find last used column
Give that code I posted a try. The function returns the column number of the
last column with data in it. You can specify a worksheet or not when you use the function... -- HTH... Jim Thomlinson "Bryan Brassell" wrote: Really what I need is the first blank column on a particular worksheet. I need to paste in a colum of numbers representing completed work. The next time a batch is run, I need to paste that set of numbers on the same sheet, in the next column, and so on... -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 "Jim Thomlinson" wrote: By last column I assume you mean the last column with a value in it anywhere??? Try this... Public Sub test() Cells(1, LastColumn() + 1).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 -- HTH... Jim Thomlinson "Bryan Brassell" wrote: I am using the following to try to identify the last used column and then select the top cell of the first empty column after the last used: myRange = ActiveSheet.UsedRange NewColumn = (myRange.Columns(myRange.Columns.Count).Column) + 1 ActiveSheet.Cells(1, NewColumn).Select Can anyone tell me what I am doing wrong? This is not working. The portion in () in the second line I copied from the help file, but do not understand. -- Regards, Bryan Brassell Padgett Business Services 281-897-9141 |
| All times are GMT +1. The time now is 01:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com