View Full Version : VBA code to delete rows based on value
Hi all,
I hope you can help me with the following:
I have a table with 2 columns (A&B) and headers. In column A values are contained in all cells and Column B has empty cells and cells with data.
So actually I need a loop with an if clause like:
If range("A").value = true AND range("B").value = true Then
EntireRow.Delete
But unfortunately I'm very bad in the looping function, so I have no clue how to get this solved.
If I missed to mention important facts, my apologies, please let me know.
Greets,
Sari
This is my code, but it's not doing what I was planned to:
In the first part of the code I copy the data from another sheet as the original (Kit Inventory List) may not be changed. The purpose of the second sheet (Empty Loc Che is to only show the rows which have missing data in column B.
Private Sub CommandButton2_Click()
'Export empty locators
ThisWorkbook.Worksheets("Empty Loc Check").Cells.Clear
Dim OutputLoc As Range
Set OutputLoc = ThisWorkbook.Worksheets("Empty Loc Check").Range("A:B")
Sheets("Kit Inventory List").Range("A:B").Copy OutputLoc
Sheets("Empty Loc Check").Rows("1").EntireRow.Delete
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With Worksheets("Empty Loc Check")
.Select
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Value = NotEmpty Then .EntireRow.Delete
End If
End With
Next Lrow
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
What do you want to delete?
empty cells or error cells?
??
Option Explicit
Sub test()
Dim c As Range
For Each c In Worksheets("Empty Loc Check").UsedRange.Columns(1).Cells
If IsEmpty(c) Then
If IsEmpty(c.Offset(, 1)) Then
c.EntireRow.Delete
End If
End If
Next
End Sub
Ummm...
why i can't reply private message
Hi mana,
empty cells. I tried your code, but unfortunately the rows with value in A and B are still populated....
Is there a possibility to see where the code makes the mistake as I don't receive any error message
Thank you very much for your help!
Sub test2()
On Error Resume Next
Worksheets("Empty Loc Check").Columns("A:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
mdmackillop
08-25-2017, 05:26 AM
Sub Test()
With Worksheets("Empty Loc Check")
Intersect(.UsedRange, .Columns(2).SpecialCells(2)).EntireRow.Delete
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.