Find the end of one column

[sandieg]

I need to find how many rows in column A in a spread sheet and then copy a formula down in Column B to the end of the data. I can copy it down but it goes to the end of the spreadsheet (6,000 rows).

Thanks

 

[dsi]

If you don't have any spaces in your data in column A, try something like this:

    Dim iRow As Long
    iRow = 1
    Do While Len(Range("A" & iRow).Text) > 0
        Range("B" & iRow).Formula = "YourFormula"
        iRow = iRow + 1
    Loop

 

[bryanbayfield]

If there aren't any spaces in your row (say row A) then I'd say it was simpler to write the following code:

Dim n as Integer
n = Range("a1",range("a1&quot.end(xldown)).cells.count - 1
Range("b1&quot.formula = "FORMULA"
Range("b1",range("b1&quot.offset(n-1,0)).filldown

Things get more complicated if you expect your range of non-blank cells in A to be non-contiguous. If this were the case you could try the following subroutine which returns the row reference of the last non-empty cell in column A. This routine could then call another sub which contains the code as above, passing the value of the last row to that routine.

Sub count()
Dim newrow As Integer, oldrow As Integer, keeprow As Integer, rowref As Integer, n as integer
Range("a1&quot.Select
rowref = 1
On Error GoTo lastline
Do While rowref < 65536
rowref = Right(ActiveCell.Address, Len(ActiveCell.Address) - 3)
oldref = rowref
ActiveCell.End(xlDown).Select
rowref = Right(ActiveCell.Address, Len(ActiveCell.Address) - 3)
newref = rowref
If Not ActiveCell.Value = &quot;&quot; Then
keepref = rowref
End If
Loop
rowref = keepref
MsgBox rowref
Exit Sub
lastline:
MsgBox rowref
End Sub


Hope this helps.

Bryan.

 

[sandieg]

thanks a bunch...that helped