Copy then paste special values to next empty row

[levi58]

I'm trying to create a macro that will copy the contents of c19:g19, and then paste special the values into the next empty row in this range, k5:036.
So far I can't figure out how to find the next empty row.
Any help would be greatly appreciated.

John

 

[JoeMo]

I'm trying to create a macro that will copy the contents of c19:g19, and then paste special the values into the next empty row in this range, k5:036.
So far I can't figure out how to find the next empty row.
Any help would be greatly appreciated.

John

Here's a UDF that you can use that will return the the next empty row (as a row number) in a range that you input as the argument.

Function LastRow(rng As Range)
Dim lRw As Long
lRw = 0
For Each rw In rng.Rows
    If WorksheetFunction.CountA(rw) = 0 Then
        lRw = rw.Row
        Exit For
    End If
Next rw
If lRw = 0 Then
    LastRow = CVErr(xlErrNA)
Else
    LastRow = lRw
End If
End Function

 

[Nalani]

This line is not clear to me.

next empty row in this range, k5:036.

I can't tell if your Range is K5 to O36. and if you are looking for the last empty cell in column K or column O.

If that is not a typo then 036 is not a valid cell.

Anyway, try this to identify your last empty row in column K. (change to suite)

LR = Range("K" & Rows.Count).End(xlUp).Offset(1).Row

 

[JoeMo]

This line is not clear to me.


I can't tell if your Range is K5 to O36. and if you are looking for the last empty cell in column K or column O.

If that is not a typo then 036 is not a valid cell.

Anyway, try this to identify your last empty cell in column K. (change to suite)

LR = Range("K" & Rows.Count).End(xlUp).Offset(1).Row

If you want to ensure that ALL cells in the row(not just those in column K) are empty, you need the UDF I posted or something like it.