VBA: How to not delete "yellow" but at the same time delete "white"

miclyzed

New Member
Joined
Apr 13, 2024
Messages
1
Office Version
  1. 365
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
I have a set of data with 2.6k rows, "yellow" portion (top and lowest) are the one that I want to keep it untouched.
I want to select the "red" rows and delete those non-highlighted ones.

Can anyone help me? Thank you.
1712997444053.png
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I want to select the "red" rows and delete those

Your thread title says delete white, but your thread text says delete red.

Book2
ABCDEFGHIJ
1Data Col 1Data Col 2Data Col 3Data Col 4Data Col 5Data Col 6Data Col 7Data Col 8Data Col 9Data Col 10
2toxonenomadismsmolossewaterpitoxamidineexpressedmainsailsupworkmanegesmetamale
3acadiearapahotarginggoldangedracemesbluisnesswhomblevellumsteriunsmugly
4calzoonsengraventathataxeroclinekryoliteintsvplayingbetoweredtrachytehoofing
5enkidufortilagebematpeshkarnintunaticinekeglerkokoonwayinghamline
6varietiesfrizzliervavasorsfaintishbackpacksfrontiersnobbleeleutbitstonekilorads
7digeneousagendumsskydovegouphaveageakkadiandemisangtigellustollbookosophy
8tektitesevadnelikestfeathererrooflinebakongoherdessinterhyalhypnoneheartdeep
9jagataicaldoseoncoticovernameexpiatistuniechevreranidshuskingmolding
10trefoilslupisohioremoldstabliergreyskinlepustodidaelobbedecus
11phonateeugenolsunknelledcistalleylepushersegaravashlockskidding
12dimmockglycininvasotomyoriasolegpolytonyadjtbraidingbottierpithsome
13digeneousagendumsskydovegouphaveageakkadiandemisangtigellustollbookosophy
14isurusetypicabasinggestorfersvavasorsreaddedmeliponaballadistgaloot
15digeneousagendumsskydovegouphaveageakkadiandemisangtigellustollbookosophy
16digeneousagendumsskydovegouphaveageakkadiandemisangtigellustollbookosophy
17digeneousagendumsskydovegouphaveageakkadiandemisangtigellustollbookosophy
18earphonesunfoldhanukkahagistedrelongrenutrompeunfallingpistareenoceanian
19eugenolspaginatesoozilyskinkingreiterfurfuralbikerslustrafacitdropsonde
20cobaltousmonroeismlieshmasticicbikerswraistfaltboatsshinessallagendums
21plonkingdrawshypnonelettingpistareenlamaiteflatbedsinhabitgrecianherminone
22flatbedsgarthsbevomitnuaduthiazidereshuffletranshapeschulnblaonersoverslur
23doorpostspadrinoagendumspogromingkissablyskiveentenderentenderfisheshumulene
Sheet1


VBA Code:
Sub DeleteRedRows()
    Dim FilterRange As Range, DataRange As Range, DeleteRange As Range
    
    With ActiveSheet
        .AutoFilterMode = False
        
        Set FilterRange = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
        With FilterRange
            Set DataRange = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
        End With
        
        FilterRange.AutoFilter Field:=1, Criteria1:=vbRed, Operator:=xlFilterCellColor
        Set DeleteRange = Application.Intersect(FilterRange.SpecialCells(xlCellTypeVisible).EntireRow, DataRange.EntireRow)
        
        If Not DeleteRange Is Nothing Then
            DeleteRange.EntireRow.Delete
        End If
        
        .AutoFilterMode = False
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,215,419
Messages
6,124,798
Members
449,189
Latest member
kristinh

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
Back
Top