Delete row if value in Column C to GK is NA

RONNYRONAY

New Member
Joined
Nov 12, 2016
Messages
4
Hey guys, I'm new to VBA programming.
I have data which contains 6 sheets and all of them has dimension of 9658 rows to A - GK columns.
I want to delete each row in every sheet if the value in column C until GK is '@NA'

How can I do that in VBA to all of 6 sheets? Thank you.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hey guys, I'm new to VBA programming.
I have data which contains 6 sheets and all of them has dimension of 9658 rows to A - GK columns.
I want to delete each row in every sheet if the value in column C until GK is '@NA'

How can I do that in VBA to all of 6 sheets? Thank you.
You want every cell in the row from col C through col GK to show a value of "@NA", or just any cell in the row from C to GK to show that value? Are there any formulas in those columns (C-GK) in any of your 6 sheets?
 
Upvote 0
You want every cell in the row from col C through col GK to show a value of "@NA", or just any cell in the row from C to GK to show that value? Are there any formulas in those columns (C-GK) in any of your 6 sheets?

The values on cell C until GK are "@NA". Yes, there are formula on those columns.

I want to delete every row if all the values on column C to GK equal to "@NA".

ABCDEGK
@NA@NA@NA@NA
@NA33@NA

<tbody>
</tbody>

First Row: Delete
Second Row: Don't Delete

Thanks for the reply. Cheers!
 
Upvote 0
The values on cell C until GK are "@NA". Yes, there are formula on those columns.

I want to delete every row if all the values on column C to GK equal to "@NA".

ABCDEGK
@NA@NA@NA@NA
@NA33@NA

<tbody>
</tbody>

First Row: Delete
Second Row: Don't Delete

Thanks for the reply. Cheers!
Try this (untested) on a copy of your workbook.
Code:
Sub DeleteIfNA()
Dim sh As Worksheet, R As Range, i As Long
Application.ScreenUpdating = False
For Each sh In Worksheets
    Set R = Intersect(Columns("C:GK"), sh.UsedRange)
    For i = R.Rows.Count To 1 Step -1
        If Application.CountIf(R.Rows(i), _
            "@NA") = Columns("C:GK").Count Then R.Rows(i).EntireRow.Delete
    Next i
Next sh
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try this (untested) on a copy of your workbook.
Code:
Sub DeleteIfNA()
Dim sh As Worksheet, R As Range, i As Long
Application.ScreenUpdating = False
For Each sh In Worksheets
    Set R = Intersect(Columns("C:GK"), sh.UsedRange)
    For i = R.Rows.Count To 1 Step -1
        If Application.CountIf(R.Rows(i), _
            "@NA") = Columns("C:GK").Count Then R.Rows(i).EntireRow.Delete
    Next i
Next sh
Application.ScreenUpdating = True
End Sub
I change the data because now I want to delete one entire row if (B:M) equals to @NA. I tried to change your code "(Columns("C:GK") into (Columns("B:M") but it says " Run-time error '1004': Method 'Intersect' of object '_Global' failed "
 
Upvote 0
I change the data because now I want to delete one entire row if (B:M) equals to @NA. I tried to change your code "(Columns("C:GK") into (Columns("B:M") but it says " Run-time error '1004': Method 'Intersect' of object '_Global' failed "
Can you post the exact revised code that produces the error?
 
Upvote 0
Can you post the exact revised code that produces the error?

Sub DeleteIfNA()
Dim sh As Worksheet, R As Range, i As Long
Application.ScreenUpdating = False
For Each sh In Worksheets
Set R = Intersect(Columns("B:M"), sh.UsedRange)
For i = R.Rows.Count To 1 Step -1
If Application.CountIf(R.Rows(i), _
"@NA") = Columns("C:GK").Count Then R.Rows(i).EntireRow.Delete
Next i
Next sh
Application.ScreenUpdating = True
End Sub

The bold one is the highlighted row when i tried to debug
 
Upvote 0
Sub DeleteIfNA()
Dim sh As Worksheet, R As Range, i As Long
Application.ScreenUpdating = False
For Each sh In Worksheets
Set R = Intersect(Columns("B:M"), sh.UsedRange)
For i = R.Rows.Count To 1 Step -1
If Application.CountIf(R.Rows(i), _
"@NA") = Columns("C:GK").Count Then R.Rows(i).EntireRow.Delete
Next i
Next sh
Application.ScreenUpdating = True
End Sub

The bold one is the highlighted row when i tried to debug
See if this works for you:
Code:
Sub DeleteIfNA()
Dim sh As Worksheet, R As Range, i As Long
Application.ScreenUpdating = False
For Each sh In Worksheets
    Set R = Intersect(sh.Columns("B:M"), sh.UsedRange)
    For i = R.Rows.Count To 1 Step -1
        If Application.CountIf(R.Rows(i), _
            "@NA") = sh.Columns("B:M").Count Then R.Rows(i).EntireRow.Delete
    Next i
Next sh
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top