Code won't fill to last row in table
 

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

Code won't fill to last row in table
 

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(xlCellTypeBlan ks) = "X"
End Sub

Regards,
Per

"Sabosis" skrev i meddelelsen
...
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

Code won't fill to last row in table
 

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

--
Rick (MVP - Excel)


"Sabosis" wrote in message
...
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

Code won't fill to last row in table
 

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.

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
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(xlCellTypeBlan ks) = "X"
End Sub

Regards,
Per

"Sabosis" skrev i meddelelsen
...
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

Code won't fill to last row in table
 

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

--
Per

"Rick Rothstein" skrev i meddelelsen
...
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.

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
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(xlCellTypeBlan ks) = "X"
End Sub

Regards,
Per

"Sabosis" skrev i meddelelsen
...
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