Log In
Register
Home
Active Member
May 23, 2020
Offline
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
See Answer
Trusted Members
December 20, 2019
Offline
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
Answers Post
February 20, 2020
Offline
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
1 Guest(s)
