Find last row through a macro

[sandip.dhamapurkar]

I have data in column A. The last row filled is row number 20
I have a formula in cell C1.

I want to detect the last filled row in column A (in this example, it
is row no.20) Once the detection is over, the vba should drag formula
in C1 till C20.

The next day I will paste some more data in column A. After running the
same macro, again the same thing should happen. Detect the NEW last row
number and drag formula from C1 till CXX (XX=NEW last row)

Can anybody help?

 

[somethinglikeant]

If I can assume that you have no empty cells between A1 and the 'last
row'
then:-

Sub FillFormulasToLastRow()
[A1].Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
qend = ActiveCell.Row
[C1].Copy Range("C2:C" & qend)
End Sub

somethinglikeant

 

[sandip.dhamapurkar]

Wonderful. Thanks.

Is there anyway to track how many empty cells are there between A1 and
the last row?

 

[somethinglikeant]

No worries, I'm getting a bit smarter with this one.
qend is now the last row, even if there are empty cells in between


Sub HowManyEmptyCells()
qend = [A65536].End(xlUp).Row
Range("A" & qend).Select: x = 0
For i = qend To 1 Step -1:
Range("A" & i).Select
If IsEmpty(ActiveCell) Then x = x + 1
Next i
MsgBox x & " Empty Cells"
End Sub

somethinglikeant

 

[Mike Fogleman]

Try this:

Range("C1", Range("A1").End(xlDown)).Offset(0, 2).FillDown

Explanation: Range("C1", -is what to filldown
Range("A1").End(xlDown) -is how far to filldown (End of column A)
..Offset(0, 2) -is which column from Range("A1") to filldown (2 columns to
the right =C)

Mike F

 

[sandip.dhamapurkar]

Super !!! Just a one liner. Will this solve the above problem? (if
there are empty cells in between A1 and last row in column A)

 

[somethinglikeant]

delete the rows with empty cells first then fill down


Sub Macro1()
qend = [A65536].End(xlUp).Row
Range("A" & qend).Select:
For i = qend To 1 Step -1:
Range("A" & i).Select
If IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete
Next i
qend = [A65536].End(xlUp).Row
Range("C1", Range("A1").End(xlDown)).Offset(0, 2).FillDown 'nice one
mike
End Sub


somethinglikeant

 

[sandip.dhamapurkar]

Combination of both. Great !! Thank you to both of you. Really
appreciate

 

[Mike Fogleman]

No, that also assumes no missing data in column A and will only filldown to
the row above the blank cell.
This will filldown to the last used cell in column A, but will also put the
formula opposite a blank cell.

Range("C1", Cells(Rows.Count, "A").End(xlUp)).Offset(0, 2).FillDown

Basically same explanation except now we determine how far up column A do we
want to filldown in column C.

Mike F