Preventing my data manipulation from #REF in excel file

See more:

VB

MS-Excel

Hello!

I'm trying to change the data of an excel file for easier import into my database. I'm doing this with 3 pieces of macro code inside the excel.

Here are the code:

1 - Delete the two first columns

Sub Del_Columns ()

Columns ("A:B").EntireColumn.Delete

End Sub

2 - Fill the right-most column with values of the above of the cell, end when reaching new value.

Sub FillBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
with wks

col = .Range("M").Column
Set rng = .UsedRange
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _ 
.Cells.SpecialCells(xlCellTypeBlanks))
On Error GoTo 0

If rng Is Nothing Then
MsgBox "No Blanks Found"
Exit Sub

Else
rng.FormulaR1C1 = "=R[-1]C"
End If

With .Cells(1, col).EntireColumn
.Value = .Value
End With
End With
End Sub

3 - Delete all rows that have empty cells inside them (Taking col F as an example, for deleting the whole row when its empty. Which it Always is)

Sub DeleteBlankRows()
On Error Resume Next
Columns ("F").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Summary

When doing this, im getting #REF on some of the cells, what should I do to prevent/fix this?

Also; Is this code better for Number 2 (Fill cells with above value):

Sub FillCellsFromAbove()

Application.ScreenUpdating = False
On Error Resume Next
With Columns(1)
.SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
.Value = .Value

End With
Err.Clear
Application.Updating = True
End Sub

What I have tried:

I have tried !distinct, but it doesnt work.