Selecting cells adjacent to non-blank cells within a loop

Question

I'm creating an airline schedule in Excel. It needs to look like this:

The look I want...

Unfortunately, text in these non-blank cells does not overlap adjacent cells, because the adjacent "blank" cells all have formulae within them (even though the value of the formulae is ""). Note that each column represents one hour of time. Thus my spreadsheet looks like this instead:

The look I've got...

I believe the only way to create the look I want is to write a macro. The process would be, for each of the eight rows shown, to loop through each column and identify non-blank cells. Then for all such non-blank cells, if the cells represent a flight departure (e.g. from WUH in blue - I can create a test for this, such as if the cell to it's left is blank), I need to select that cell and the four cells to its right, and then merge-and-left-justify. If the cells represent a flight arrival (e.g. WUH in red, or MCO), I need to select the cell, copy the value-only of its contents to the cell 4 to its left, then select that cell and the four to its right, then merge-and-right-justify.

Can someone please help me with this code, as I am a complete novice at VBA? The part I need the most help on is selecting a range of cells adjacent to a non-blank cell during the looping process. Note I'm also a novice at Stack Overflow, so if I have failed to ask the question properly, please let me know.

Welcome to StackOverflow. Please note, that this is not a free code-writing service, but we are eager to help fellow programmers (and aspirants) writing their own code. Please read the help topics on How To Ask a Good Question. Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. — Skip Intro
I don't think you need VBA, it does not solve that the cells next to the one you would like to see are not empty. Instead move the cells with the formulas somewhere else, maybe into hidden columns. — z32a7ul
You could probably start with recording a macro that copies the steps you describe. — Skip Intro
Thank you for your comments. I will try using hidden columns - thanks for that suggestion. I have tried recording a macro but I think in VBA I would still need a loop so that the formatting is responsive to schedule changes. If I go the VBA route I will post my code. thanks again. — Bob D.

Bob D. Bob D. · Accepted Answer · 2016-11-01T19:30:21

FYI, I solved my problem with the following macro:

Sub FormatRotation()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim c As Integer
Dim r As Integer

For c = 4 To 362
'Test if column contains an arrival
    If Len(Cells(9, c).Text) > 2 And Cells(9, c + 1) = "" Then
       For r = 9 To 16
            Cells(r, c).Select
            Selection.Copy
            Cells(r, c - 4).Select
            Selection.PasteSpecial Paste:=xlValues
        Next r
        For r = 9 To 16
            Range(Cells(r, c - 4), Cells(r, c)).Select
                With Selection
                .MergeCells = True
                .HorizontalAlignment = xlRight
                End With
        Next r

    End If

'Test if column contains a departure
    If Len(Cells(9, c).Text) > 2 And Cells(9, c + 1) <> "" Then
        For r = 9 To 16
            Range(Cells(r, c), Cells(r, c + 4)).Select
                With Selection
                .MergeCells = True
                .HorizontalAlignment = xlLeft
                End With
        Next r
    End If


Next c

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Calculate

End Sub

Selecting cells adjacent to non-blank cells within a loop

1 Answers