Last Column With Data
 

How do I check a certain row to find out the last column in the row that has
data?

Last Column With Data
 

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you haven't
used any cells in that row.

Is that a problem?



Bishop wrote:

How do I check a certain row to find out the last column in the row that has
data?

--

Dave Peterson

Last Column With Data
 

This gives a interger value but I can't make it work with this statement:

..Columns("B:" & LastCol).EntireColumn.AutoFit

Do I need to set LastCol as something other than Long? Or is there a
different syntax I should use to make the above statement work?

"Dave Peterson" wrote:

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you haven't
used any cells in that row.

Is that a problem?



Bishop wrote:

How do I check a certain row to find out the last column in the row that has
data?

--

Dave Peterson
.

Last Column With Data
 

Try changing this line...

lastcol = .cells(myrow,.columns.count).end(xltoleft).column

to this...

lastcol = split(.cells(myrow,.columns.count).end(xltoleft).a ddress, "$")(1)

and see if that works for you.

--
Rick (MVP - Excel)



"Bishop" wrote in message
...
This gives a interger value but I can't make it work with this statement:

.Columns("B:" & LastCol).EntireColumn.AutoFit

Do I need to set LastCol as something other than Long? Or is there a
different syntax I should use to make the above statement work?

"Dave Peterson" wrote:

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you
haven't
used any cells in that row.

Is that a problem?



Bishop wrote:

How do I check a certain row to find out the last column in the row
that has
data?

--

Dave Peterson
.

Last Column With Data
 

You could use:

..range("b1",.cells(1,lastcol)).entirecolumn.Autof it
or
..range("b1",.columns(lastcol)).entirecolumn.autof it



Bishop wrote:

This gives a interger value but I can't make it work with this statement:

.Columns("B:" & LastCol).EntireColumn.AutoFit

Do I need to set LastCol as something other than Long? Or is there a
different syntax I should use to make the above statement work?

"Dave Peterson" wrote:

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you haven't
used any cells in that row.

Is that a problem?



Bishop wrote:

How do I check a certain row to find out the last column in the row that has
data?

--

Dave Peterson
.


--

Dave Peterson

Last Column With Data
 

Well that worked perfect. But now I can't figure out what's wrong with this
statement:

..PageSetup.PrintArea = ("$A$1:" & .Cells(LowerPrintArea, LastCol))

I also tried:

..PageSetup.PrintArea = ("$A$1", .Cells(LowerPrintArea, LastCol))

and

..PageSetup.PrintArea = "$A$1", .Cells(LowerPrintArea, LastCol)

but neither of those worked either. I'm sure I'm just messing up the syntax
and Excel help was no help. Can you advise?

"Dave Peterson" wrote:

You could use:

..range("b1",.cells(1,lastcol)).entirecolumn.Autof it
or
..range("b1",.columns(lastcol)).entirecolumn.autof it



Bishop wrote:

This gives a interger value but I can't make it work with this statement:

.Columns("B:" & LastCol).EntireColumn.AutoFit

Do I need to set LastCol as something other than Long? Or is there a
different syntax I should use to make the above statement work?

"Dave Peterson" wrote:

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you haven't
used any cells in that row.

Is that a problem?



Bishop wrote:

How do I check a certain row to find out the last column in the row that has
data?

--

Dave Peterson
.


--

Dave Peterson
.

Last Column With Data
 

If you leave off the property you want, then excel will (always???) use .value.

So .Cells(LowerPrintArea, LastCol) would refer to the value in that cell. If it
looked like an address, it would work -- but probably not the way you want!

But try specifying .address as the property you want:

..PageSetup.PrintArea = "$A$1:" & .Cells(LowerPrintArea, LastCol).Address

or use a range and let excel do the work for you:

..PageSetup.PrintArea = .range("A1", .Cells(LowerPrintArea, LastCol)).Address



Bishop wrote:

Well that worked perfect. But now I can't figure out what's wrong with this
statement:

.PageSetup.PrintArea = ("$A$1:" & .Cells(LowerPrintArea, LastCol))

I also tried:

.PageSetup.PrintArea = ("$A$1", .Cells(LowerPrintArea, LastCol))

and

.PageSetup.PrintArea = "$A$1", .Cells(LowerPrintArea, LastCol)

but neither of those worked either. I'm sure I'm just messing up the syntax
and Excel help was no help. Can you advise?

"Dave Peterson" wrote:

You could use:

..range("b1",.cells(1,lastcol)).entirecolumn.Autof it
or
..range("b1",.columns(lastcol)).entirecolumn.autof it



Bishop wrote:

This gives a interger value but I can't make it work with this statement:

.Columns("B:" & LastCol).EntireColumn.AutoFit

Do I need to set LastCol as something other than Long? Or is there a
different syntax I should use to make the above statement work?

"Dave Peterson" wrote:

Dim myRow as long
dim LastCol as long

myrow = 1243

with worksheets("SomeSheetnamehere")
lastcol = .cells(myrow,.columns.count).end(xltoleft).column
end with

msgbox LastCol

This will break if you've used the last column in that row -- or if you haven't
used any cells in that row.

Is that a problem?



Bishop wrote:

How do I check a certain row to find out the last column in the row that has
data?

--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson