Filling the first empty cell

[Guest]

I'm new to VBA with excel, so be gentle. I'm having trouble writing a loop to go down a column and find the first empty cell and then fill it with a value from my form. I think I have the logic down, but I keep getting errors so I thought an example would be helpful. Thanks

 

[Gord Dibben]

Rick

Not sure if you mean "first" blank row or "first" blank row at bottom? Could
be different.

To copy B1 to first empty Cell at bottom of column A use this.

Sub Copyit()
Range("B1").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub

To copy B1o first blank Cell in Column A use this.

Sub Copyit22()
Range("B1").Copy Destination:= _
ActiveSheet.Cells.End(xlDown) _
.Offset(1, 0)
End Sub

Gord Dibben Excel MVP

 

[Tom Ogilvy]

Dim rng as Range
set rng = Cells(1,1)
do while not isempty(rng)
set rng = rng.offset(1,0)
Loop

would loop down the row as you described.

if the data is contiguous you could do

Dim rng
set rng = cells(rows.count,1).End(xlup)
if not isempty(rng) then set rng = rng.offset(1,0)

--
Regards,
Tom Ogilvy

I'm new to VBA with excel, so be gentle. I'm having trouble writing a

loop to go down a column and find the first empty cell and then fill it with
a value from my form. I think I have the logic down, but I keep getting
errors so I thought an example would be helpful. Thanks