Guna13
Board Regular
- Joined
- Nov 22, 2019
- Messages
- 70
- Office Version
- 365
- Platform
- Windows
Hi
Can someone support, why does not support this code to delete "C" Column I have #/N/A lines. Where I need to modify this code. Appreciate your Support.
Can someone support, why does not support this code to delete "C" Column I have #/N/A lines. Where I need to modify this code. Appreciate your Support.
VBA Code:
Sub Not_appl_Data_Delete()
Dim Trg As Worksheet
Dim a As Variant, b As Variant
Dim nc As Long, i As Long, k As Long
Set Trg = ActiveSheet '<- Perhaps you have a worksheet name instead?
With Trg
nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
a = .Range("C2", .Range("C" & Rows.Count).End(xlUp)).Value
ReDim C(1 To UBound(a), 1 To 1)
For i = 1 To UBound(a)
If a(i, 1) = "#N/A" Then
b(i, 1) = 1
k = k + 1
End If
Next i
If k > 0 Then
Application.ScreenUpdating = False
With .Range("A2").Resize(UBound(a), nc)
.Columns(nc).Value = b
.Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
.Resize(k).EntireRow.Delete
End With
Application.ScreenUpdating = True
End If
End With
End Sub