Find the last nonblank cell

**janjan_376** · 08-20-2009, 12:24 PM

Hi. Please help me this scenario.

I need to locate the last cell (that contains value) in column J. Then once that cell is located, an "x" should be marked on the next cell beside it (column K).
Example:

J1:J10 contains value
J11 is blank
J12:J50 contains value
J51:J53 is blank
J54:J100 contains value
J101 is blank or contains no value

then an "x" should be automatically marked on cell K100.

Please help. Thank you.

**Palmetto** · 08-20-2009, 01:45 PM

Give this a try:

Sub Last_Used_Row()

    Dim lrow As Long
    
    lrow = Cells(Rows.Count, 10).End(xlUp).Row 'column 10 = J
    
    Cells(lrow, 10).Offset(0, 1).Value = "X"

End Sub

It goes into a standard module.

**janjan_376** · 08-20-2009, 03:06 PM

Thank you very much. It worked perfectly. I really appreciated your help.

I need another help though related to this one also. Now that it is able to put x on column K, I would like to copy the value of cell K1 down to the cell where its marked "x".

Please help. Thank you.

**Paul** · 08-20-2009, 03:42 PM

Hi JanJan,

The following one line of code will find the last used row in column J and fill down the value from K1 through that row. It doesn't put an X in that cell, but it could be adjusted to do so if necessary (it puts the value from K1 into the cell, as with all the other cells above it).

Sub fillDownK()
  Range("K1:K" & Range("J:J").Find(What:="*", After:=Range("J1"), LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).FillDown
End Sub

**janjan_376** · 08-21-2009, 12:26 PM

You guys are amazing!!!! Thank you Palmetto, thank you Paul. It worked perfectly.

Thank you again. I really appreciated your help. Thanks.

**janjan_376** · 08-21-2009, 12:39 PM

I have another challenging idea which of course need help from you talented guys.
Now that it is able to copy everything (from k1 to the last cell). Is it possible to skip the cell (blank the cell) in column K if the cell in column J is blank:

example:
if J1:J10 contains value, copy K1 down to K10
if J11:J15 contains no value, K11:K15 should be blank as well
if J16:J20 contains value, K16 down to K20 should contain the value of K1.

Thanks again. Please help.