CODEWITHSUNDEEP
excelvbaexcel-formula
Benjamin Perry
Benjamin Perry

Reputation: 85

VBA - Replace blank cells

I have to replace each blank cell in the column BM with the data in this same row in column DN. I have written that: 

Columns("BM:BM").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=DN2"

But the "=DN2" ends like "=@'DN2'" in every cell. Which makes an error. Does someone know how could I get the cell in DN from the same row please?

Thank you very much in advance!

Upvotes: 2

Views: 1131

Answers (3)

JvdV
JvdV

Reputation: 75840

Here are my two cents. I would drop the idea of using SpecialCells on whole columns altogether. If you want to go with SpecialCells then atleast create a Range within the BM column first to use it on. If any column exceeds the rows of interest you'll end up with values in places you really don't want (formulas in your case).

Here is a small example spanning A1:C5:

enter image description here

Our area of interest is column B:B where, if empty, we need the values from column C:C

When we run code using SpecialCells, for example: 

Columns("B:B").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC3"

We will end up with:

enter image description here

What we see is that SpecialCells makes use of UsedRange (as @Peh rightfully mentioned in the comment section) and therefor is highly unreliable when used like that. There is a risk of ending up with values/formulas where you don't want them. I suggest making use of the last used row from column C:C (or in your case DN) and go from there. So allready much saver would be something like:

.Range("BM1:BM" & <LastUsedRowOfDN>).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC118"

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149277

Another way to do this

Dim rng As Range

On Error Resume Next
Set rng = Columns("BM:BM").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
    rng.Formula = "=DN" & rng.Row
End If

BTW if you are using SpecialCells, use error handling else your code will crash when it doesn't find those cells

Upvotes: 4

Scott Craner
Scott Craner

Reputation: 152450

If you are going to R1C1 then make the Formula R1C1

If Application.CountA(ActiveSheet.Range("BM:BM"))<> ActiveSheet.Rows.Count Then
    ActiveSheet.Range("BM:BM").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC118"
End If

Upvotes: 4

Related Questions