xlTypeBlanks/xlTypeVisible selecting wrong range

[ClaireS]

I have some simple code that copies values from one workbook into another. When this is done, I want to add a timestamp (in a variable called dtStaticDate) to the copied cells in both the source and destination files. I need to select the blank cells in a table called "StaffWork" in the column headed "Logged". I have been trying this code:

Range("StaffWork[Logged]").SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = dtStaticDate

This sometimes works fine, but sometimes (and I don't know what makes it happen) it selects either the entire sheet or all the cells in and above the table. What am I doing wrong? There are always one or more new cells to log, as I don't do this if no records have been copied. I have tried filtering the table and using xlTypeVisible instead, but the problem persists.

 

[JLeno]

In that case you could modify your code to:

With Range("StaffWork[Logged]")
  If .Count > 1 Then .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = dtStaticDate
End With

That would actually not capture the case that the named range consists of 1 empty cell, if a timestamp is needed in that case.

 

[ClaireS]

Thanks all very much. I have gone with a slightly modified version of JackDanIce's code, as it is more elegant, bumping out of the IF on the first option once the table has got going! This is what I'm using now:

 With Range("StaffWork[Logged]")
            If .Cells.Count > 1 Then
                .SpecialCells(xlCellTypeBlanks).Value = dtStaticDate
            ElseIf .Value = "" Then
                    .Value = dtStaticDate
            End If
        End With

The suggested versions seemed a bit short on "End if", "End With", so it took me a while to compile, but it's all good now.

 

[ClaireS]

BTW I'm not checking whether the size of the range = 1 cell on the ElseIf, as it's a table in Excel 2010 - so if it's not greater than 1 it must BE 1.