asked on # Filling all the non contiguous blank cells in a column with consecutive numbers

Hi Everybody,

Its first time I am using this website. I have a specific problem I cannot find the solution to anywhere. The data I am trying to fix come from a psychology experiment.

So I have a column of data populated by numbers from 0 to 20. The column has unpredictable blank cells in between those numbers like so:

blank, blank,blank,4,blank,blank,3,blank, blank,blank,blank,5,blankx15, etc

i need to fill all the blanks starting from 0 and incrementing up in each adjacent empty cell up to the next non-empty cell, then move down to the next empty cell and do the same until the end.

This data comes from an application which measures behavioural responses. So each empty cell is a response which the participant was correct, and the cells with numbers are responses which the participant made a mistake, at which point, the total of correct responses is entered into the cell. So the non-empty cell is essentially the sum of all the empty cells above it plus itself.

Thanks a lot.

Its first time I am using this website. I have a specific problem I cannot find the solution to anywhere. The data I am trying to fix come from a psychology experiment.

So I have a column of data populated by numbers from 0 to 20. The column has unpredictable blank cells in between those numbers like so:

blank, blank,blank,4,blank,blank,

i need to fill all the blanks starting from 0 and incrementing up in each adjacent empty cell up to the next non-empty cell, then move down to the next empty cell and do the same until the end.

This data comes from an application which measures behavioural responses. So each empty cell is a response which the participant was correct, and the cells with numbers are responses which the participant made a mistake, at which point, the total of correct responses is entered into the cell. So the non-empty cell is essentially the sum of all the empty cells above it plus itself.

Thanks a lot.

VB ScriptMicrosoft Excel

Can you please post an example with before and after data?

Before After

**3** ** 3**

0

1

2

3

4

**5** **5**

0

1

**2** ** 2**

**0** ** 0**

0

1

2

**3** ** 3**

so the numbers in bold are the original numbers, the empty space in the column**Before** are the empty cells I want to fill :).

0

1

2

3

4

0

1

0

1

2

so the numbers in bold are the original numbers, the empty space in the column

Thanks.

Assuming your list is in column A and starting at row 2, you can use this formula in an adjacent column...

Cell B2 Formula - =IF(A2<>"", A2, IF(AND(A1<>"", A2=""), 0, D1+1))

Copy the formula down as required. You can then copy that column and Paste Values over the original data in column A.

Assuming your list is in column A and starting at row 2, you can use this formula in an adjacent column...

Cell B2 Formula - =IF(A2<>"", A2, IF(AND(A1<>"", A2=""), 0, D1+1))

Copy the formula down as required. You can then copy that column and Paste Values over the original data in column A.

Thank you so much mr Taylor. I will try this first thing in the morning. I was hoping for a vba solution though - I have some other things I need doing. But if this works, this is still great :).

Since you didn't mention the column where you have those numbers with blanks, one way to achieve this is select the range with numbers and blanks in them and run the following code and it will fill the blanks with the desired output.

```
Sub FillNumbers()
Dim rng As Range
On Error Resume Next
For Each rng In Selection.SpecialCells(xlCellTypeBlanks).Areas
rng.Formula = "=ROWS(A$1:A1)-1"
rng.Value = rng.Value
Next rng
End Sub
```

Hi mr Tiwari,

The column is colum N and the data starts from row 2. If I understand correctly, I need to put a line of code before the For Next loop to select the correct range? I know how to use the xlDown formula but I think the empty cells will block the selection to the end. In any case I am sure I can find the solution for this. Thanks so much. This can automate a very complicated data file.

The column is colum N and the data starts from row 2. If I understand correctly, I need to put a line of code before the For Next loop to select the correct range? I know how to use the xlDown formula but I think the empty cells will block the selection to the end. In any case I am sure I can find the solution for this. Thanks so much. This can automate a very complicated data file.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

Mr Tiwari,

Thank you so much. Your solution worked fantastic :). Thanks a lot.

Thank you so much. Your solution worked fantastic :). Thanks a lot.

You're welcome Dritan! Glad to help.

Thanks for the feedback.

Thanks for the feedback.

Can I ask another question please? I am now copying that column into a row in a different sheet. I have the following code, see below. But it runs only the first If statement. Once the loop exits from the first if statement, I want it to go to the next if statement, but it exits. I dont' know why!

```
Sub testmethod()
Dim count1 As Long
Dim count2 As Long
Dim count3 As Long
Dim count4 As Long
Dim count5 As Long
Dim count6 As Long
Dim myValue As Long
count1 = 2
count2 = 14
count3 = 2
count4 = 1
count5 = 2
count6 = 2
rawData.Activate ' activating the relevant worksheet
Range("A2").Select ' selecting the reference point
If ActiveCell.Value = "106" Then ' these are codes, different number refers to different person
Do
myValue = ActiveCell.Offset(0, 13).Value ' assign the value 13 cells to the right of the active cell to a long variable
myData.Activate ' the next sheet where I want to paste the data
Cells(count5, count6).Value = myValue ' pasting the data in the relevant cell
count6 = count6 + 1 'moving one column to the right on the same row
rawData.Activate 'back to the worksheet to copy the data
ActiveCell.Offset(1, 0).Select ' move one row down
Loop While ActiveCell.Value = "106" 'loop while that condition holds
ElseIf ActiveCell.Value = "108" Then
count5 = count5 + 1 ' moves the counter so that in the copying to sheet i start copying one row down for the next participant.
Do
myValue = ActiveCell.Offset(0, 13).Value
myData.Activate
Cells(count5, count6).Value = myValue
count6 = count6 + 1
rawData.Activate
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.Value = "108"
End If
End Sub
```

Dritan,

Please open a New Question for your other requirement. :)

Please open a New Question for your other requirement. :)

Ok, will do, but I don't want to lose you there :)

Post the link of your new question here. I will follow you up there if I am available. :)

Thank you sir Tiwari, you are the best :).

You're welcome Dritan! Glad to help.

Thanks for the compliment. :)

Thanks for the compliment. :)