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.

 

[JackDanIce]

Welcome to the board. What does this do:

Range("StaffWork[Logged]").SpecialCells(xlCellTypeBlanks).Value = dtStaticDate

 

[ClaireS]

Theoretically, it selects just the blank cells in the table column called "Logged", and sets the value of these cells to the timestamp held in dtStaticDate. But sometimes, it selects the entire worksheet and splatters the timestamp over the whole thing - not a very satisfactory result!

 

[JLeno]

Do you use an 'On Error Resume Next' statement in your code?

If so, if no blank cells are found, the procedure continues with some previous selection.

 

[RoryA]

If the Logged range only had one cell, then Specialcells would apply to the entire sheet and not just that range - could that be your issue?

 

[ClaireS]

JLeno - No, I haven't tried that yet, though it doesn't seem to throw an error... RoryA - This could explain it, as currently I'm working with just a few records of test data. Do you mean it happens when the entire field range is just one cell, or does it happen if there's just one blank cell to select?

 

[RoryA]

If the entire field range is one cell.

 

[JLeno]

JLeno - No, I haven't tried that yet, though it doesn't seem to throw an error... RoryA - This could explain it, as currently I'm working with just a few records of test data. Do you mean it happens when the entire field range is just one cell, or does it happen if there's just one blank cell to select?

I wasn't asking so you can try it, I would advise not to.

If the named range is just 1 cell, Excel will expand the selection to the entire (used portion of the) sheet. You could change the code to

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

 

[ClaireS]

That looks like a great solution, JLeno, and RoryA, thanks for the explanation - it explains why I was getting the problem when I cleared down the data and tried a test with just a record or two in each staff worksheet. Back to the code window!

 

[JackDanIce]

In that case you could modify your code to:

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