# If Statements and dealing with blank cells

#### terrykalaka

##### New Member
Hey Gang,

Long time lurker, first time poster here! I've searched around a for a couple of days to try to figure out my deal here, but haven't been able to find a solution, so here goes.

The problem I'm working on is for creating start lists for a cycling event. We start with a Sign In worksheet like this:

 A B C D E F G H 1 No. Name Lic # Cat. State TT IP 2 1 96 John Stevenson 58245 1 GA x x 3 2 68 Steven James 23654 3 GA x 4 3 10 Don Johnson 44038 4 GA x 5 4 32 Chad Christie 64573 2 GA x x

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

The TT and IP columns are the two different events, x in each of columns G and H column for riders signing up for that particular event. From that, we create a start/time sheet using formula:

To get this Start List worksheet:

 A B C D E 1 Heat Start Position No. Men's 1000m TT Time 2 1 Front 96 John Stevenson 3 Back 4 2 Front 10 Don Johnson 5 Back 32 Chad Christie 6 7 Heat Start Position No. Men's 4k IP Time 8 1 Front 96 John Stevenson 9 Back 68 Steven James 10 2 Front 11 Back 32 Chad Christie

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Everything works great on that part, except for the blank cells that are left from riders in the original Sign In sheet not doing a particular event. Right now, we manually go through all of the lists and remove the blank cells. This is where I'd like to automate that process, so that in the case that the IF statement returned a blank cell, that it would read the next cell from the Sign In sheet until it returned an actual value. What do you guys think?

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
If you dont want blank, show a different character.

If you dont want blank, show a different character.

It's not that they don't want a blank, it's that they want to skip to the next filled line. This formula is a formula as it returns C2 based on G2. I don't get your statement of it being the same as no formula.

As for the OP, it sounds like a visual basic problem because the only possible thing I can think of is a nested index/match/offset formula that might get messy.

Welcome to the forum.

This is not easy to do with a formula but simple to do with a macro.

Code:
``````Sub lineup()
Dim currRow As Long
Dim lastRow As Long
Dim heat As Integer
Dim outRow As Long

Application.ScreenUpdating = False
Cells(1, 1) = "Heat"
Cells(1, 2) = "Start Position"
Cells(1, 3) = "No."
Cells(1, 4) = "Men's 1000m TT"
Cells(1, 5) = "Time"

outRow = 2
heat = 1
For currRow = 2 To lastRow
If outRow Mod 2 = 0 Then
Cells(outRow, 1) = heat
Cells(outRow, 2) = "Front"
Else
Cells(outRow, 2) = "Back"
End If
outRow = outRow + 1
End If
Next
outRow = outRow + 3
Cells(outRow - 1, 1) = "Heat"
Cells(outRow - 1, 2) = "Start Position"
Cells(outRow - 1, 3) = "No."
Cells(outRow - 1, 4) = "Men's 4K IP"
Cells(outRow - 1, 5) = "Time"
heat = 1
For currRow = 2 To lastRow
If outRow Mod 2 = 0 Then
Cells(outRow, 1) = heat
Cells(outRow, 2) = "Front"
Else
Cells(outRow, 2) = "Back"
End If
outRow = outRow + 1
End If
Next
End Sub``````

Thanks for the replies, after my search I had been thinking it would be something more intense than a regular formula could handle. par60056, thanks for the tip, I'll give it a shot and see how it goes, thanks!

I just realized there is a line missing from the macro.

Application.ScreenUpdating = True

Right before the "End Sub"

I just realized there is a line missing from the macro.

Application.ScreenUpdating = True

Right before the "End Sub"

The worst line to forget....

DARN IT EXCEL WHY WON'T YOU LET ME TYPE!!! GAH!!!!!!

The worst line to forget....

DARN IT EXCEL WHY WON'T YOU LET ME TYPE!!! GAH!!!!!!

Funny thing is that the macro ran find in testing and everything looked right. But I bet it would mess with your mind later.

Before I learned of the 'Immediate' window I would have my own macro call to turn on/off anything I forgot to turn on/off. Before that...I usually just shut it all down if I forgot that step.

With it as a macro you can then add formatting (text wrap, bold, column widths outlines) and have a lot of fun

Replies
7
Views
646
Replies
7
Views
891
Replies
7
Views
536
Replies
3
Views
782
Replies
3
Views
605

1,217,316
Messages
6,135,816
Members
449,965
Latest member
Ckl43

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back