Delete row where specific cell is blank or meets condition

S

scotawful

Board Regular

Joined

Mar 6, 2003

Messages

148

• Jun 16, 2003

• #2

Re: Delete row where specific cell is blank or meets conditi

does this mean you want to be checking for multiple values in the rows for deletion?? I can probably provide some code, but I'm not exactly sure what it is you've got in mind.

 

Upvote 0

hip2b2

Board Regular

Joined

May 5, 2003

Messages

135

Office Version

1. 2019

Platform

1. Windows

• Jun 16, 2003

• #3

Re: Delete row where specific cell is blank or meets conditi

No, only looking for one condition to be met, ( most likley the if cel in col B, the delete row).

Thanks

hip

 

Upvote 0

XL-Dennis

Well-known Member

Joined

Jul 27, 2002

Messages

1,920

• Jun 16, 2003

• #4

Re: Delete row where specific cell is blank or meets conditi

Hi,

You may find one possible solution here:

http://216.92.17.166/board2/viewtopic.php?t=54274

Kind regards,
Dennis

 

Upvote 0

S

scotawful

Board Regular

Joined

Mar 6, 2003

Messages

148

• Jun 16, 2003

• #5

Re: Delete row where specific cell is blank or meets conditi

Here's probably someting simmilar to what you want:

Code:

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
    Range("A" & i).EntireRow.Delete
    End If
  Next i
  
End Sub

 

Upvote 0

hip2b2

Board Regular

Joined

May 5, 2003

Messages

135

Office Version

1. 2019

Platform

1. Windows

• Jun 16, 2003

• #6

Re: Delete row where specific cell is blank or meets conditi

I would prefer something simpler, where I enter the column and variable in the macro itself. Unfortunatly, I can't quite seem to rearrange your macro to do this for me.

Many thanks


hip ray:

 

Upvote 0

D

dolphin

Board Regular

Joined

Nov 18, 2002

Messages

159

• Jun 17, 2003

• #7

Re: Delete row where specific cell is blank or meets conditi

Another possinle solution:

You can delete an entire row based on the value in a particular column or range using something like this

Sub Deleteit()
Dim c As Range
For Each c In Selection
If c.Value = Range("$B$1").Value Then c.EntireRow.Delete
Next
End Sub

Select the column (Say Col A) and run this macro.
If the value of a cell in Column A = $B$1, then the entire row will be deleted

 

Upvote 0

I

IAteASeagull

New Member

Joined

Oct 22, 2008

Messages

13

• Jul 30, 2009

• #8

Re: Delete row where specific cell is blank or meets conditi

I used the code below, however it only deletes a row if the value is exactly what I enter when inputting a value for MyVal. Here's what I'm dealing with:

I have 10,000 emails in one column. I need to delete a row if part of the email's string is generic (ie: "yahoo.com", "gmail.com", etc...). I believe this can be done with the code below, as it works if the string is exactly the same as what's inputted when running the macro; however, I don't care what comes before the generic email provider. Any ideas?

scotawful said:

Here's probably someting simmilar to what you want:

Code:

Sub myDeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("column to look through", "Column Search", "A")
MyVal = InputBox("Value to look for", "search value", 0)
  For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
    If Application.WorksheetFunction.CountIf(Range("A" & i & ":AZ" & i), MyVal) > 0 Then
    Range("A" & i).EntireRow.Delete
    End If
  Next i
  
End Sub

Click to expand...

 

Upvote 0

J

jagweb7

New Member

Joined

Nov 17, 2008

Messages

8

• Aug 3, 2010

• #9

Re: Delete row where specific cell is blank or meets conditi

Hello - is it possible to use this macro on a defined column range instead of the whole column? The way it seems to run is for the whole entire column even though my test has 10 rows of data. THX

Sub Deleteit()
Dim c As Range
For Each c In Selection
If c.Value = Range("$B$1").Value Then c.EntireRow.Delete
Next
End Sub

 

Upvote 0

C

Craig1

Active Member

Joined

Jun 11, 2009

Messages

322

• Aug 4, 2010

• #10

Hi Guys,

Learnt a lot from this thread but as other learners I need some more help.
I am using the code below to delete rows based on an empty cell in column K

Range("K:K").SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.delete
Range("A1").Select 'Get back to top of Sheet

The code above works perfect to delete the rows based on all the column.

Not sure how to modify this code but I don't want the code to start looking at the rows until it gets to K8, which is the first row under the headers.
I also need to duplicate the code to then look in column C (again from C8) and delete the rows if the 8 digit number begins with a 1.

Thanks in advance.

Craig.

 

Upvote 0