PDA

View Full Version : VBA code to delete rows based on value



Sari
08-25-2017, 03:00 AM
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

mana
08-25-2017, 03:25 AM
Please post your code.

Sari
08-25-2017, 03:51 AM
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

mana
08-25-2017, 04:24 AM
What do you want to delete?
empty cells or error cells?

mana
08-25-2017, 04:39 AM
??


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

Sari
08-25-2017, 04:59 AM
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!

mana
08-25-2017, 05:13 AM
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