Last Column With Data

[Bishop]

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

 

[Dave Peterson]

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]

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?

 

[Rick Rothstein]

Try changing this line...

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

to this...

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

and see if that works for you.

 

[Dave Peterson]

You could use:

..range("b1",.cells(1,lastcol)).entirecolumn.Autofit
or
..range("b1",.columns(lastcol)).entirecolumn.autofit

 

[Bishop]

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]

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