If Blank

Resolved · High Priority · Version 2007

Fri 4 Oct 2013, 13:57 Delegate Jason said...

Jason has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

If Blank

Hi,

I have a column which has up to 6 blank cells in between data. For example;

ABCD

EFGH

IJKL

Is there a formula i could use to populate the missing cells within the data above until the content changes? The end result would be as follows?

ABCD
ABCD
ABCD
EFGH
EFGH
EFGH
IJKL
IJKL

It is not necessary for the formula to be in within the blank spaces.

Many thanks

Fri 4 Oct 2013, 16:45 Trainer Doug said...

RE: If Blank

Hi Jason

Filling the blank cells is not very easy to d with a formula. It is however possible with a macro. Here is the macro code for your example with the first item starting in cell A1.

Sub FillText()

Dim myText As String

Range("A1").Select

Do Until ActiveCell = "IJKL"

If ActiveCell <> "" Then
myText = ActiveCell.Value
Else: ActiveCell = myText
End If
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value

End Sub

Let me know if you want to use this approach and I will be happy to step you through and modify the macro for you.

Doug Dunn
Best STL

Mon 7 Oct 2013, 16:23 Delegate Jason said...

RE: If Blank

Hi Doug,

Thanks for the response, i am happy to go with your suggested approach. If you could take me through the logic that would be great.

thanks

Mon 7 Oct 2013, 16:37 Trainer Doug said...

RE: If Blank

Hi Jason.
Probably easiest by telephone. If yo are free now call me on
07714175078. Or you could leave me a number and I'll call you at lunch time tomorrow?

Regards
Doug

Thu 10 Oct 2013, 09:33 Trainer Doug said...

RE: If Blank

Hi Jason
This is the logic of the fillText macro.
Dim myText as String
Defines a variable or temporary storage slot to hold text such as ABCD.
Range("A1").select
Change this to match the cell reference of the first cell (in your example where ABCD is located)
Do until activecell="IJKL"
Repeat the code below up to the Loop continuously until the active cell contains the text IjKL.
If activecell<>"" then
MyText =activecell.value
This means if the cell is not blank then store the text (eg ABCD) in the variable MyText for later use
Else: activecell=myText
This will replace the blank cell with what is stored in MyText
So the data will now read
ABCD
ABCD

EFGH

IJKL etc

Activecell.offset(1,0).select
Means move the active cell down on cell

Let me know how you get on!
Doug Dunn
Best STL