VBA. Fill empty cells with text "OPEN", I2 downwards, row count Col A

**rain4u** · 10-17-2011, 05:47 PM

Hi
What would be the most efficient or easiest VBA to achieve below:

VBA would fill all the all the empty cells in col I with value "OPEN". This would be from cell I2 (inclusive) downwards as far as the rows go in col A.

So the below would change:
From this:

-------------- -------------- Col A ----------------------------- Col I
Row 1 -------------- order number ---------- Schedule State
Row 2 -------------- Order 1 ---------------------- RECEIVED
Row 3 -------------- Order 2 ---------------------- RECEIVED
Row 4 -------------- Order 3 ---------------------- RECEIVED
Row 5 -------------- Order 4 ---------------------- --------------
Row 6 -------------- Order 5 ---------------------- --------------
Row 7 -------------- Order 6 ---------------------- --------------
Row 8 -------------- Order 7 ---------------------- --------------
Row 9 -------------- -------------- ---------------------- --------------
Row 10 -------------- -------------- ---------------------- --------------
Row 11 -------------- -------------- ---------------------- --------------
Row 12 -------------- -------------- ---------------------- --------------
Row 10 -------------- -------------- ---------------------- --------------

To this:

-------------- -------------- Col A ----------------------------- Col I
Row 1 -------------- order number ---------- Schedule State
Row 2 -------------- Order 1 ---------------------- RECEIVED
Row 3 -------------- Order 2 ---------------------- RECEIVED
Row 4 -------------- Order 3 ---------------------- RECEIVED
Row 5 -------------- Order 4 ---------------------- OPEN
Row 6 -------------- Order 5 ---------------------- OPEN
Row 7 -------------- Order 6 ---------------------- OPEN
Row 8 -------------- Order 7 ---------------------- OPEN
Row 9 -------------- -------------- ---------------------- --------------
Row 10 -------------- -------------- ---------------------- --------------
Row 11 -------------- -------------- ---------------------- --------------
Row 12 -------------- -------------- ---------------------- --------------
Row 10 -------------- -------------- ---------------------- --------------

Columns are always arranged by column I so the empty cell ones will always be at the bottom. It would be nice to have a solution that does not loop. Any help is always appreciated.

**tom1977** · 10-17-2011, 05:54 PM

If You are interested in solution without VBA then select range “I441: I1” press F5 (special) mark empty cells, put “OPEN” text and hit CTRL + ENTER

with VBA

Please Login or Register  to view this content.

**JBeaucaire** · 10-17-2011, 06:37 PM

Tom, you suggest a manual method that uses the Special Cells method, great! But then a VBA approach that doesn't use the same efficient technique?

Please Login or Register  to view this content.

**rain4u** · 10-17-2011, 06:38 PM

Good stuff mate.
Much appreciated!

**tom1977** · 10-17-2011, 06:42 PM

JBeaucaire
Of course You right it's thousand times faster..
regards

**rain4u** · 10-17-2011, 06:44 PM

Originally Posted by JBeaucaire

Tom, you suggest a manual method that uses the Special Cells method, great! But then a VBA approach that doesn't use the same efficient technique?

Please Login or Register  to view this content.

Errors out. " Method 'Range' of object '_Global' failed.

with line below:
LR = Range("A " & Rows.Count).End(xlUp).Row

I like the 2 line code. Any ideas?

**rain4u** · 10-17-2011, 06:50 PM

Please Login or Register  to view this content.

Got it. Simple typo. extra space "A ". Should read "A"

As its probably impossible to do it any faster than this, I will put it as solved.
Thank you both.