Last Column With Data

  • Thread starter Thread starter Bishop
  • Start date Start date
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?
 
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?
 
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.
 
You could use:

..range("b1",.cells(1,lastcol)).entirecolumn.Autofit
or
..range("b1",.columns(lastcol)).entirecolumn.autofit
 
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?
 
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
 
Back
Top