Macro to delete rows based on multi cell value

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
390
Office Version
  1. 2019
Platform
  1. Windows
Hi i use the following code to delete rows if cells in columns F and G are empty,
Could someone adjust it so it would delete rows if cells in column F G and H are empty ?

VBA Code:
Sub DeleteRows1()
' Defines variables
Dim x As Long, LastRow As Long, cRange As Range


' Defines LastRow as the last row of data based on column D
LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
' Sets the check range as D1 to the last row of D
Set cRange = Range("h15:g" & LastRow)


' For each cell in the check range, working from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
        ' If column A, B and C of the current row are all empty then...
        If Application.WorksheetFunction.CountIf(Range("f" & .Row, "g" & .Row), "") = 2 Then
            ' Delete that row
            .EntireRow.Delete
        End If
    End With
' Check next cell in check range, moving from the bottom upwards
Next x

' Display optional message to confirm the task is finished
MsgBox "Complete"


End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
Try changing this line:
VBA Code:
        If Application.WorksheetFunction.CountIf(Range("f" & .Row, "g" & .Row), "") = 2 Then
to
VBA Code:
        If Application.WorksheetFunction.CountIf(Range("f" & .Row & ":" & "h" & .Row), "") = 3 Then
or simply:
VBA Code:
        If Application.WorksheetFunction.CountBlank(Range("f" & .Row & ":" & "h" & .Row)) = 3 Then
 
Solution

ste33uka

Active Member
Joined
Jan 31, 2020
Messages
390
Office Version
  1. 2019
Platform
  1. Windows
Try changing this line:
VBA Code:
        If Application.WorksheetFunction.CountIf(Range("f" & .Row, "g" & .Row), "") = 2 Then
to
VBA Code:
        If Application.WorksheetFunction.CountIf(Range("f" & .Row & ":" & "h" & .Row), "") = 3 Then
or simply:
VBA Code:
        If Application.WorksheetFunction.CountBlank(Range("f" & .Row & ":" & "h" & .Row)) = 3 Then
Thanks alot , that works
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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
Top