Find last used column

[Guest]

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

 

[Guest]

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

 

[Jesse]

It's kind of unnecessary.

myRange=ActiveSheet.UsedRange.Columns.Count
ActiveSheet.Cells(1,myRange +1).Select

Works for me.

Jesse

 

[Guest]

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

 

[Jesse]

myRange=Sheets(DestinationSheet).UsedRange.Columns.Count
ActiveSheet.Cells(sourcedata).EntireColumn.Copy
Destination:=Sheets(DestinationSheet).Cells(1,myRange +1).EntireColumn

Jesse

 

[Jim Cone]

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



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

 

[Guest]

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.

 

[Jesse]

myRange=Sheets(DestinationSheet).UsedRange.Columns.Count
ActiveSheet.Cells(sourcedata).EntireColumn.Copy _
Destination:=Sheets(DestinationSheet).Cells(1,myRange
+1).EntireColumn


Sorry, auto word wrap.

Jesse

 

[Guest]

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...