Filling the first empty cell

R

Rick

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
 
G

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
 
T

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





Rick said:
I'm new to VBA with excel, so be gentle. I'm having trouble writing a
Click to expand...
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
 
Top