Blanks

[tony_d]

Hi
Got to be a way to do this I filter by column A and it shows if any cells are blank but text in B to G
I manually type in Blank I use a simple macro to do this bit it only works in the last cell is there a better way below is what I use.

ActiveSheet.Range("$A$1:$G$3000").AutoFilter Field:=1, Criteria1:="="
Range("B65000").End(xlUp).Offset(0, -1).Select

ActiveCell.FormulaR1C1 = "Blank"

Any help would be good

 

[Yongle]

Test this on a COPY of your worksheet

Option Explicit
Sub BlankIt()
    Dim cel As Range, txt As String, a As Integer
        For Each cel In Range("A1").CurrentRegion.Resize(, 1)
             If Len(cel) = 0 Then
                txt = ""
                For a = 1 To 6
                   txt = txt & cel.Offset(, a).Value
                Next a
                If Len(txt) > 0 Then cel = "Blank"
            End If
        Next cel
End Sub

 

[Yongle]

or simpler ..

Sub BlankIt2()
    Dim cel As Range
    For Each cel In Range("A1").CurrentRegion.Resize(, 1)
        If Len(cel) = 0 And WorksheetFunction.CountA(cel.Resize(, 6).Offset(, 1)) > 0 Then cel = "Blank"
    Next cel
End Sub