VBA for selecting blank cells and populating with a value

[Hen25]

Hi,

I have a large data to analyse from which I want to identify blank cells in column A and populate it with value in adjacent cells from column F. If column F is blank then populate the value from adjacent cell in column K and if column K is also blank then fill 'Check' as the value in cell.

Does anyone know how I can achieve this using a VBA macro for dynamic data? Please help.

 

[cerfani]

select the entire table and then turn on the autofilter, then filter it by blanks and set the cells equal to column F#

edit: oh nm, u said vba

Sub Hey()
    Dim r, startRow, endRow As Long

    startRow = 2
    endRow = Cells(Rows.Count, "B").End(xlUp).Row 'pick a column that has no blanks

    For r = startRow To endRow
        If IsEmpty(Cells(r, "A")) Then
            If IsEmpty(Cells(r, "F")) Then
                If IsEmpty(Cells(r, "K")) Then
                    Cells(r, "A") = "Check"
                Else
                    Cells(r, "A") = "=K" & r
                End If
            Else
                Cells(r, "A") = "=F" & r
            End If
        End If
    Next r
End Sub

 

[Rick Rothstein]

Here is another macro you can try...

Sub FillBlanksInColumnAFromForK()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row
  Range("A1:A" & LastRow) = Evaluate(Replace("IF(A1:A#<>"""",A1:A#,IF(F1:F#<>"""",F1:F#,IF(K1:K#="""",""Check"",K1:K#)))", "#", LastRow))
End Sub