Delete certain N\A

[MasmaAbdulhamidli]

Hi. I have multiple rows with datas but also N\A. I need to delete N\A till there is beginning value by each row. I have sample, can you help? I need only delete N/A till becomes value. In "tomato" row there is no need delete N/A because of first value is number

apple	N/A	1	2
peach	N/A	N/A	3
tomato	1	N/A	3

 

[rpaulson]

try this on a copy of your file.

I assume the N/A are in Column B

Sub DeleteRows() 'This macro will delete the entire row if there is a data match anywhere in the range.

On Error Resume Next
    With Range("B:B")
        .Replace "N/A", False, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
On Error GoTo 0
End Sub

 

[MasmaAbdulhamidli]

try this on a copy of your file.

I assume the N/A are in Column B

Sub DeleteRows() 'This macro will delete the entire row if there is a data match anywhere in the range.

On Error Resume Next
    With Range("B:B")
        .Replace "N/A", False, xlWhole
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
On Error GoTo 0
End Sub

Thank you very much. I'll try this. But are we sure that this code will not delete N/A when it is after from value? sorry for my english. I hope i can explain

 

[Joe4]

rpaulson,

I believe the mean deleting only the N/A values in each row preceding the first non-N/A value (and shifting the cells, not deleting the whole row), so you are left with something like this:

Masma,

Is that correct?

If so, are you dealing with literal TEXT values on "N/A", or is it the "#N/A" error?
And in what cell does your data start?

 

[MasmaAbdulhamidli]

rpaulson,

I believe the mean deleting only the N/A values in each row preceding the first non-N/A value (and shifting the cells, not deleting the whole row), so you are left with something like this:

View attachment 107374

Masma,

Is that correct?

If so, are you dealing with literal TEXT values on "N/A", or is it the "#N/A" error?
And in what cell does your data start?

apple		1	2
peach			3
tomato	1	N/A	3

it is "#N/A" error but i can change to text format. I need result like this. Only delete first N/A until becomes any number value and there will be blanks at the place of N/A row by rows. But i want if there is N/A middle of values making it stay as it be

 

[Joe4]

OK, since you did not give me the ranges, you will need to adjust my code to match where your data is found.

Sub MyDeleteCells()

    Dim lr As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last cell in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting on row 1
    For r = 1 To lr
'       Check values in column B
        Do
'           If value in N/A, delete it
            If IsError(Cells(r, "B")) Then
                Cells(r, "B").Delete Shift:=xlToLeft
            Else
                Exit Do
            End If
        Loop
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[MasmaAbdulhamidli]

Please look at this

 

[MasmaAbdulhamidli]

OK, since you did not give me the ranges, you will need to adjust my code to match where your data is found.

Sub MyDeleteCells()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last cell in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows, starting on row 1
    For r = 1 To lr
'       Check values in column B
        Do
'           If value in N/A, delete it
            If IsError(Cells(r, "B")) Then
                Cells(r, "B").Delete Shift:=xlToLeft
            Else
                Exit Do
            End If
        Loop
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Thank you. I can set columns

 

[Joe4]

OK, my code will not work for you (it would have been really helpful to have that last image right from the very beginning!)
It looks like you just want to clear those values, but not delete/shift them over to the left.
I will need to re-work my code.

 

[Joe4]

OK, this version should do what you want:

Sub MyClearErrorsFromCells()

    Dim lr As Long, lc As Long
    Dim r As Long, c As Long
    
    Application.ScreenUpdating = False
    
'   Find last cell in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
'   Find last column in row 3 with data
    lc = Cells(3, Columns.Count).End(xlToLeft).Column
    
'   Loop through all rows, starting on row 3
    For r = 3 To lr
'       Loop through columns starting in column B
        For c = 2 To lc
'           Check to see if there is an error in cell
            If IsError(Cells(r, c)) Then
                Cells(r, c).ClearContents
            Else
'               Exit loop if no error in cell
                Exit For
            End If
        Next c
    Next r
    
    Application.ScreenUpdating = True
    
End Sub