Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA code to fill down | Excel Discussion (Misc queries) | |||
macro code for XML Fill | Excel Programming | |||
sub won't fill down via code | Excel Programming | |||
List Fill Code | Excel Programming | |||
VBA Code for a pivot table to open database and to reference table current page | Excel Programming |