Prev: Is Error Help on a formula
Next: If Statement Nesting?
From: Mathew on 30 Mar 2010 14:19
Micky: Thanks, but that does the same thing as my code, what I need is for
the last cloumn with data in it within the range A3: AD97. Any ideas on
that?

"מיכאל (מיקי) אבידן" wrote:

> This might help:
> LR = ActiveSheet.UsedRange.Rows.Count
> LC = ActiveSheet.UsedRange.Columns.Count
> and so, Cells(LR, LC) is the lest cell in the used range
> Micky
>
>
> "Mathew" wrote:
>
> > I have a very large range, say A3 to AD97. Within this data, there needs to
> > be additional data added weekly. What I want to do is add the data, via a
> > macro, that will add the data on the row and in the last column of data plus
> > 1.
> > lastcoll = Cells(3, Columns.Count).End(xlToLeft).Column this gives me the
> > last column with data in row 3, but I have data in cells beyond AD, so how
> > do I have this code work for the entire Range(a3, AD97) .
> > I tried:
> >
> > dim r as range
> > r = Range(a3, AD97) , this line gives me run time error 1004,
> >
> > Also, I have 94 unique names in column A. How would I find the row number
> > within this range A3:A97 like Jacmel?
> >
> > Any help would be greatly appreciated!
> >
From: Mike H on 30 Mar 2010 14:34
Matthew,

The only way I could get it was with looping. so until someone comes up with
something better you could use this

Dim LastCol As Long
For x = 3 To 97
LastCol = WorksheetFunction.Max(LastCol, _
ActiveSheet.Cells(x, 30).End(xlToLeft).Column)
Next

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mathew" wrote:

> Micky: Thanks, but that does the same thing as my code, what I need is for
> the last cloumn with data in it within the range A3: AD97. Any ideas on
> that?
>
> "מיכאל (מיקי) אבידן" wrote:
>
> > This might help:
> > LR = ActiveSheet.UsedRange.Rows.Count
> > LC = ActiveSheet.UsedRange.Columns.Count
> > and so, Cells(LR, LC) is the lest cell in the used range
> > Micky
> >
> >
> > "Mathew" wrote:
> >
> > > I have a very large range, say A3 to AD97. Within this data, there needs to
> > > be additional data added weekly. What I want to do is add the data, via a
> > > macro, that will add the data on the row and in the last column of data plus
> > > 1.
> > > lastcoll = Cells(3, Columns.Count).End(xlToLeft).Column this gives me the
> > > last column with data in row 3, but I have data in cells beyond AD, so how
> > > do I have this code work for the entire Range(a3, AD97) .
> > > I tried:
> > >
> > > dim r as range
> > > r = Range(a3, AD97) , this line gives me run time error 1004,
> > >
> > > Also, I have 94 unique names in column A. How would I find the row number
> > > within this range A3:A97 like Jacmel?
> > >
> > > Any help would be greatly appreciated!
> > >
 | 
Pages: 1
Prev: Is Error Help on a formula
Next: If Statement Nesting?