Code won't fill to last row in table

S

Sabosis

Hello-

I have a code to fill all blanks in column F with an "X". There is
data in Columns A:E down to row 2145, but the code only puts the X
down to row 2135. Cell 2136 is the last filled cell in column F, this
has something to do with it. I want the code to search column F based
on the entire range of column E, in this case E2:E2145.

Sub b()

Dim lngLastRow As Long

Application.ScreenUpdating = False

For lngLastRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row To 2
Step -1
If Cells(lngLastRow, "F") = "" Then
Cells(lngLastRow, "F").Value = "X"
End If
Next lngLastRow

Application.ScreenUpdating = True


End Sub

Please help if possible
 
P

Per Jessen

Hi

Your code has to look at column E, when it determine last row:

For lngLastRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row To 2 Step -1

It can also be done with this one line:

Sub FillX()
Range("F2", Range("F" &
Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks) = "X"
End Sub

Regards,
Per
 
R

Rick Rothstein

Then change the "F" in your in the For statement to "E". However, you don't
have to loop to do this; the following should do what you want...

Sub AssignXs()
Range("F2:F" & Cells(Rows.Count, "E").End(xlUp).Row). _
SpecialCells(xlCellTypeBlanks).Value = "X"
End Sub
 
R

Rick Rothstein

That will do what the OP's code does... but it is using the wrong "last
row"... the OP wants the last row in Column E to be applied to Column F's
range.
 
P

Per Jessen

You are absolutely right.... Obviously I wasn't alert, when I changed it to
a single line statement....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top