Fill Blanks

[vmjan02]

the below code works very well, but if there isn't any blank cells it gives a error, any idea as how to modif the same.


Sub Fill_Blanks()
With Range("M5:M" & Range("J" & Rows.Count).End(xlUp).Row)
.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
End Sub

 

[Tim_Excel_]

Please use

 tags when pasting code.

First, I don't see the use of 
[CODE][COLOR=#333333].Value = .Value[/COLOR]

It's like saying 1=1. It's always true, it never won't be true, and it won't do anything.

Sub Fill_Blanks()
On Error Resume Next

With Range("M5:M" & Range("J" & Rows.Count).End(xlUp).Row)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
End With

On Error GoTo 0
End Sub

This would work if this is the COMPLETE Sub. It ignores the code and lets the code continue. If there are more lines than the ones you've provided, I would be hesitant in recommending you this option.

 

[vmjan02]

thanks tim, for quick response. No there are not another other lines.

 

[Tim_Excel_]

Glad to have helped

 

[MARK858]

Please use

First, I don't see the use of 
[CODE][COLOR=#333333].Value = .Value[/COLOR]

It's like saying 1=1. It's always true, it never won't be true, and it won't do anything.

It has a use, it converts the formula to a constant.

 

[Tim_Excel_]

Cheers Mark, another day another thing learned

 

[Fluff]

Further to what Mark858 said (and irrelevant in this scenario)
If you have a formula that returns "" & you use pastespecial you will end up with a null string in the cell (ie not blank), but using

[COLOR=#333333].Value = .Value[/COLOR]

will make those cells blank

 

[vmjan02]

Got It, Thanks ton Fluff.

 

[Fluff]

You're welcome