• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

IF #N/A delete entire row VBA help!|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / IF #N/A delete entire row VBA help!|VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosIF #N/A delete entire row VBA help!
sp_PrintTopic sp_TopicIcon
IF #N/A delete entire row VBA help!
Avatar
Shafiq Rehman

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
May 23, 2020
sp_UserOfflineSmall Offline
1
May 23, 2020 - 5:05 am
sp_Permalink sp_Print

Hi Everyone

I am not very good at using VBA!. I have an excel table (say Table A) that is linked to another table (say Table B) in the same workbook, anther worksheet. When the figures are updated in that Table B, Table A gives error in certain rows as the data is not found. I want to delete all such rows with #N/A error at once using a VBA code. 

Sample file is attached.

Thanks & best regards

Shafiq 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 414
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
May 23, 2020 - 2:28 pm
sp_Permalink sp_Print

Hi Shafiq

Try this - it looks in each cell in column c and if its an error it deletes it - a word of warning it will delete the entire row if the cell is ANY error (this can be modified) and it is only based on column c so if any other cells on that row were good they also would be deleted. Again this can be modified if needed.

Up to you if you want this as part of a bigger module or as a 'tidy up' button at the end.

Sub DeleteNAs()

Dim lr As Integer
Dim i As Integer

lr = Cells(Rows.Count, 3).End(xlUp).Row

For i = lr To 6 Step -1
Debug.Print i
If IsError(Range("c" & i).Value) Then

Rows(i).EntireRow.Delete

End If
Next i

End Sub

Purfleet

sp_AnswersTopicAnswer
Answers Post
Avatar
Miguel Santos
Member
Members
Level 0
Forum Posts: 80
Member Since:
February 20, 2020
sp_UserOfflineSmall Offline
3
May 24, 2020 - 7:49 am
sp_Permalink sp_Print
Hello,
Another way to delete all rows independently which cell has the error

Miguel

 

Private Sub DeleteRowsWithError() '  delete all rows with error in cells

Dim Firstrow As Long
Dim LastRow As Long
Dim FirstColumn As Long
Dim LastColumn As Long
Dim lRow As Long
Dim lCol As Long
Dim CalculateApp As Long
Dim AppView As Long

With Application
     CalculateApp = .Calculation
     .Calculation = xlCalculationManual
     .ScreenUpdating = False
End With

With ActiveSheet
     .Select
     AppView = ActiveWindow.View
     ActiveWindow.View = xlNormalView

     .DisplayPageBreaks = False

     Firstrow = .UsedRange.Cells(1).Row
     LastRow = GetLastRow(ActiveSheet)
     FirstColumn = .UsedRange.Cells(1).Column
     LastColumn = GetLastColumn(ActiveSheet)

     For lRow = LastRow To Firstrow Step -1
          For lCol = LastColumn To FirstColumn Step -1
               With .Cells(lRow, lCol)
                    On Error Resume Next
                    If IsError(.Value) Then .EntireRow.Delete
               End With
          Next lCol
     Next lRow

ActiveWindow.View = AppView

End With

With Application
     .ScreenUpdating = True
     .Calculation = CalculateApp
End With

End Sub

Public Function GetLastRow(ByVal Sht As Worksheet) ' finding the last Row

Dim lRow As Integer
lRow = Sht.Cells.SpecialCells(xlLastCell).Row

Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1
     lRow = lRow - 1
Loop

GetLastRow = lRow

End Function

Public Function GetLastColumn(ByVal Sht As Worksheet) ' finding the last column

Dim lCol As Integer
lCol = Sht.Cells.SpecialCells(xlLastCell).Column

Do While Application.CountA(Sht.Columns(lCol)) = 0 And lCol <> 1
     lCol = lCol - 1
Loop

GetLastColumn = lCol

End Function

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Merav Tzori, Mercedes McCay-Read
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 217
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
David du Toit
leandro barbarini
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27796

 

Member Stats:
Guest Posters: 49
Members: 32326
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.