Use VBA to copy cell into next blank cell continuously

[GMC The Macro Man]

Hi there
I have a daily report that I need to run a Macro on however some of the detail in Column D (Ticket) is missing. What I need is the Macro to look at Column D only and if there is a BLANK cell then to copy the Cell above that has Data.
So Cells D3 & D4 will become Ticket -806, Cells D6 & D7 will become Ticket -804, D9 will become Ticket -802, D14 & D15 will become Ticket -803 and so on until the end of the column. Once that is done I can then continue with my Macro.
Any help would be appreciated
Thanks in Advance

 

[Arturthemaster]

Use a macro to merge cells and set a shortcut to be faster

 

[GMC The Macro Man]

Sorry I should have added one caveat to this. If Cell D3 is empty, then it has to check if Cell C3 has data, if so then copy the Ticket Number in to D3. If Cell C3 was BLANK, then the MACRO should stop. This would be important when it came to the last cell in column D with data so it would stop if there was no data on the cell to its immediate left

 

[Joe4]

Try this code:

Sub MyFillBlanks()

    Dim lr As Long
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Assign formulas to blank cells in column D
    Range("D1:D" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    
'   Convert formulas to hard-coded values
    Range("D1:D" & lr).Value = Range("D1:D" & lr).Value
    
End Sub

 

[Joe4]

Use a macro to merge cells and set a shortcut to be faster

I would HIGHLY recommend NOT doing that!
Merge cells are an absolute disaster and should be avoided whenever possible!
Most experienced users will never use them, due to all of the issues that they cause for things like VBA, sorting, etc.

 

[Joe4]

Here is an updated version of the code that makes sure that there is actual data on the sheet, and ignores any errors if there are not any blanks:

Sub MyFillBlanks()

    Dim lr As Long
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Confirm that there is data
    If lr < 2 Then Exit Sub
   
    On Error Resume Next
'   Assign formulas to blank cells in column D
    Range("D2:D" & lr).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
   
'   Convert formulas to hard-coded values
    Range("D2:D" & lr).Value = Range("D1:D" & lr).Value
    On Error GoTo 0
   
End Sub

 

[Arturthemaster]

I would HIGHLY recommend NOT doing that!
Merge cells are an absolute disaster and should be avoided whenever possible!
Most experienced users will never use them, due to all of the issues that they cause for things like VBA, sorting, etc.

What happens its the tickets does reference for more than one line, this is the reason I recommended that.

 

[Fluff]

Merged cells are an abomination & should be avoided like the plague. Recommending people use them is madness.

 

[Joe4]

My solution does not care if you need one line or multiple lines. It handles it just fine.
The solution is just the VBA equivalent of the method described here: How to fill blank cells with value above / below / left / right in Excel?

See here more a discussion on merged cells: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[Arturthemaster]

My solution does not care if you need one line or multiple lines. It handles it just fine.
The solution is just the VBA equivalent of the method described here: How to fill blank cells with value above / below / left / right in Excel?

See here more a discussion on merged cells: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Nice, I dont thought on that