Excel VBA - Test rollover to next blank cell when values set in VBA

Question

Is anyone aware of how to trigger text to rollover to the next blank cell if it's set in vba. I have the following code which is setting the values correctly removing duplicate values. My preference would have been to merge the cells to show the extended value where possible however as the data is in a table I'm not able to do this.

Public Sub GANT_TidyRow(ByVal argRow As Range, argDateTableRange As Range)

Dim lclLastValue As String
Dim lclFirstCell As Boolean
Dim lclCurrCell As Range
Dim lclCellRange As Range

Set argRow = argRow.Worksheet.Cells(argRow.Cells(1, 1).Row, argDateTableRange.Cells(1, 1).Column)
Set argRow = argRow.Resize(1, argDateTableRange.Columns.Count)
lclFirstCell = True

For Each lclCurrCell In argRow

    If lclCurrCell.Value = lclLastValue And Not lclFirstCell Then
        lclCurrCell.Value = ""
        Set lclCellRange = lclCellRange.Resize(1, lclCellRange.Columns.Count + 1)
    Else
        If Not lclFirstCell Then
            Call GANT_SetBorders(lclCellRange.Borders, _
                        xlNone, _
                        xlSingle, vbBlack)
        End If
        Set lclCellRange = lclCurrCell
        lclFirstCell = False
        lclLastValue = lclCurrCell.Value
    End If

Next lclCurrCell
End Sub

Here's a screenshot, the first 2 rows were updated in vba to have the following cell set to blank. The last row I manually updated in excel to clear out the value and then the previous cell 'rolls over' in to the next one. There may well be a more common and accurate term for this please let me know if so. I can't post images due to insufficient rep, but here's a screen shot I took. http://imgur.com/Ti8InmL

When you say "text rollover to the next blank cell," are you saying you want the blank cells to have the same value as the cell in the row above it? — Cyril
I'd like to merge cells but as the data is in a table it's not possible to, I've added a picture now. Excel by default will do a kind of roll-over merge when the text doesn't fit in the current box, so I'm taking this as an alternative solution to merging the cells. However for some reason this doesn't work when I'm setting the blank value in the cell to be overwritten in VBA (it works when setting manually in excel). — borg pants

Cyril Cyril · Accepted Answer · 2017-08-04T12:59:48

~~Assuming the interpretation in my comment is correct, you can do an if statement to check if blank, then fill in if not blank. A generic code for this would be:~~

For i = 1 to LastRow
    If ISEMPTY(Cells(i,1))=True Then
        Cells(i,1).Value=Cells(i-1,1).Value
        End If
Next i

For the case of your code, you should be able to do something like:

For each lclCurrCell in argRow
    If ISEMPTY(lclCurrCell)=True Then
        lclCurrCell.Value=lclCurrCell.Offset(-1,0).Value
        End If
Next

Edit: Updating to match suit the post.

Try:

For each lclCurrCell in argRow
    If ISEMPTY(lclCurrCell)=True Then
        lclCurrCell.ClearContents
        Else
        End If
Next

Setting the value to "" is not the same as removing the contents of the cell. You should get that "rollover" where the column A row 1 text shows up in column B row 1 when there is no content, as opposed to forcing it to "".

Excel VBA - Test rollover to next blank cell when values set in VBA

1 Answers