0

I have a worksheet where I want to fill down the entire while the result of column B's fill down is nonempty. Column B contains the links to other workbooks that are updated routinely. Thus if column B is nonempty, I can proceed to the rest of the macro.

In other words, I am trying to fill down column B first, and if it's nonempty, I will fill down the entire row. 

While Range("b1048575").End(xlUp).FillDown <> 0
    Range("a1048575").End(xlUp).offset(0, 200).Select
    Selection.FillDown

Wend

However, the condition in the while loop results in the program halting.

I thought of ways where I'd fill down the row anyways and check column B, and if it's empty, delete the row and get out of the loop.

I am wondering if there is a more efficient approach here.

Feel free to critique my code (I don't think the use of a1048575 is very "professional").

user101998
  • 241
  • 5
  • 15
  • I'm not sure I understand your question entirely. Are you just trying to fill column A down to the same row as the last row in column B? – BigBen Jun 05 '19 at 15:45
  • 1
    Instead of `a1048575`, use `Cells(Rows.Count, 1)`. – BigBen Jun 05 '19 at 15:47
  • @BigBen I am trying to fill the entire last row of the worksheet, if the result of the fill of column B is nonempty. Column B contains links that are updated routinely. – user101998 Jun 05 '19 at 15:48
  • Can you add a sample screenshot maybe? Not sure I follow. – BigBen Jun 05 '19 at 15:50
  • 1
    @BigBen Can't access image hosting site in where I'm at. I can add it later. But the idea is that I would like to fill down an entire row, but if column B is empty after filling down, then I don't want to fill it down. (The fill down are done so that the rest of the code can proceed to do other stuff, using the information on that row. So if column B is empty after filling down, the rest of the code would result in an error). – user101998 Jun 05 '19 at 15:52
  • Ok I think I have a better idea. – BigBen Jun 05 '19 at 15:53
  • 2
    `While...Wend` is deprecated, use `Do While...Loop` instead. Then if/when you need to break out of the loop, you can do `Exit Do` instead of `GoTo`-jumping. – Mathieu Guindon Jun 05 '19 at 15:56
  • You should try to avoid using select aswell. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – erazorv4 Jun 07 '19 at 07:18

0 Answers0