Last cell creates circular reference!

[aapp81]

i have several columns which all differ in lengths (some have more rows
than others...

i already have a sub that will find the longest column... but now i
need a COUNTA formula i can write in that cell that will only calculate
A$2:A$last cell in column... and i can't use A:A b/c my formula is in
that row so it'll create a circ. ref. and i can't create another column
b/c this is the way it needs to be calculated...
also, i need this formula to be able to paste into another cell and
it'll change the column (A to C, etc...) accordingly...
please help!

 

[steve smallman]

OK, so you are using a sub to find the last cell in a
column, therefore, you need to use the sub to insert your
formula.

whichever method you use to determine the last cell,
should be able to return a reference of that last cell.

Assuming you start at Column A use endXldown to find last
row, and then go right one column using activecell.offset
(0,1), and test whether or not that cell has a value,
then check if the next row has a value and if yes, use
end XL down, and loop until you have completed your
column range; you can return the activecell.row property
(say 275), add 1 to the returned value, and then select
cell A276 and use something like

dim scol as string
scol = Chr$(ActiveCell.Column + 64)
ActiveCell.Formula = "=counta(" & scol & "2:" & scol &
ActiveCell.Row - 1 & ")"

enclose the 2 lines within a loop to cover your column
range, and it should work

Steve

 

[aapp81]

thanks for the reply this is what i'm trying to do:

Sub CountProducts()
Dim scol As String
Dim selRange As Range

'THIS WILL SELECT ALL MY DATA MINUS THE HEADING
Range("A2").Select
Set SelRng = Selection.CurrentRegion
If SelRng.Row = 1 Then _
Set SelRng = SelRng.Offset(1, 0).Resize(SelRng.Rows.Count - 1)
SelRng.Select

'THIS IS YOUR CODE WHICH WORKS GREAT
scol = Chr$(ActiveCell.column + 64)
ActiveCell.Formula = "=counta(" & scol & "2:" & scol & ActiveCell.Row
1 & ")"

End Sub

but i'm trying to incorporate this:
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A" & LastRow + 3).Select
so that it lists the "result" or counta of your code in the lastrow+
of columns A:S

but don't forget all my columns are different lenghts so if the longes
one has 40 rows than all the counta must be in A43, B43, C43, ....
etc..