Prev: With Sheets("wildcard")
Next: MACRO BASED ON A NUMBER OF CHARACTERS
From: Bishop on 5 May 2010 17:08
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.Autofit
> or
> ..range("b1",.columns(lastcol)).entirecolumn.autofit
>
>
>
> 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
> .
>
From: Dave Peterson on 5 May 2010 20:37
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.Autofit
> > or
> > ..range("b1",.columns(lastcol)).entirecolumn.autofit
> >
> >
> >
> > 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
First  |  Prev  | 
Pages: 1 2
Prev: With Sheets("wildcard")
Next: MACRO BASED ON A NUMBER OF CHARACTERS