Seardh for value in column a and delete depending

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
Hi there,

I am sure I remember a similar question being asked but I cant seem to find it. I have a series of values in column a and if this value is eg. alpha or beta, I want the macro to delete the whole row but I cant seem to figure out how to do this. Cana nyone give me a pointer?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
This should do that:

Code:
Sub macro1()
Dim lngRow As Long, lngLastRow
Const strMyColumn = "B"
    
    lngLastRow = Range(strMyColumn & 65536).End(xlUp).Row
    lngRow = 2
    Do While lngRow <= lngLastRow
        If Range(strMyColumn & lngRow) = "alpha" Or Range(strMyColumn & lngRow) = "beta" Then
            Rows(lngRow).Delete
            lngLastRow = lngLastRow - 1
        Else
            lngRow = lngRow + 1
        End If
    Loop
End Sub
 

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
Thanks ver much, I have modified as below but it doesn't appear to do anything. Any ideas?


Sub macro2()
Dim lngRow As Long, lngLastRow
Const strMyColumn = "B"

lngLastRow = Range(strMyColumn & 65536).End(xlUp).Row
lngRow = 2
Do While lngRow <= lngLastRow
If Range(strMyColumn & lngRow) = "1w" Or Range(strMyColumn & lngRow) = "2w" Or _
Range(strMyColumn & lngRow) = "1m" Or Range(strMyColumn & lngRow) = "2m" Or _
Range(strMyColumn & lngRow) = "4m" Or Range(strMyColumn & lngRow) = "5m" Or _
Range(strMyColumn & lngRow) = "7m" Or Range(strMyColumn & lngRow) = "8m" Or _
Range(strMyColumn & lngRow) = "9m" Or Range(strMyColumn & lngRow) = "10m" Or _
Range(strMyColumn & lngRow) = "11m" Then
Rows(lngRow).Delete
lngLastRow = lngLastRow - 1
Else
lngRow = lngRow + 1
End If
Loop
End Sub
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
This is a quick operation in case there are a lot of rows.
This is presuming you have a header row in row 1 and there are no empty rows. There are still other methods. This one is using autofilter.
kind regards,
Erik

Code:
Sub AutoFilter_Delete()
Dim Rng As Range
Range("A1").AutoFilter , Field:=1, Criteria1:="alpha"
Set Rng = Range("A2:A" & Range("A1").End(xlDown).Row) 
Rng.EntireRow.Delete
Range("A1").AutoFilter , Field:=1, Criteria1:="betha"
Set Rng = Range("A2:A" & Range("A1").End(xlDown).Row) 
Rng.EntireRow.Delete
Range("A1").AutoFilter , Field:=1
Range("A1").AutoFilter
End Sub

EDIT: didn't see your last post
here you'll see where I learned this kind of things "long ago" :)
http://www.mrexcel.com/board2/viewtopic.php?t=83086
 

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
Thanks ver much, I have modified as below but it doesn't appear to do anything. Any ideas?
As was mentioned, there are many other options. I tested your code and it worked fine for me. It deletes anything matching your criteria in column B starting with row 2. The only error I see is if you have data all they way down to row 65536, but other than that it should run. Does the data in the cells have an extra space or even an apostrophe in them before the "1w", etc.?
 

tmischler

Well-known Member
Joined
Jun 17, 2004
Messages
669
whoops - thanks - I neglected to notice which column it was looking at!
 

Forum statistics

Threads
1,148,268
Messages
5,745,774
Members
423,970
Latest member
josearistud

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