Macro to delete rows based on cell values in a range - dynamic rows and columns

Dany46

New Member
Joined
Dec 2, 2020
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. MacOS
Hello team,

I would like to ask for help with the following macro as I just can't figure it out, I tried so many versions but nothing worked for me.

I need to remove all rows in my sheet that do not contain value greater than 50 in any cell in a given row. That means that if the value is less than 50 in all cells in a row but one, I need to keep this row and to only delete it if all the cells contain values that are less than 50 or are blank. However, my range is dynamic (both rows and columns are dynamic) which is causing the main issue for me as I don’t know how to specify the range correctly. The first three columns are text only, so I need the macro to only search for values from the fourth column until the last column and from the second row (the first one is header) until the last row. I need to “count” the number of rows based on the first column as it is the only column where no cell will ever be blank, e.g. this way: LastRow = Range("A" & Rows.Count).End(xlUp).Row

What would be of extra help but is not necessary – to change interior color of cells that are greater than 50 to RGB (255, 199, 206).

Thank you very much for any advice, it will be very appreciated!

Dany
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi & welcome to MrExcel.
How about applying conditional formatting to the range & use this formula
Excel Formula:
=COUNTIF($D2:$AZ2,">50")
You can then filter col D on colour & delete the visible rows.
 
Upvote 0
Hello Dany46,
here is one fresh code,
Try it.
VBA Code:
Option Explicit

Sub KeepGreaterRows()
    
    Dim varWS As Worksheet
    Dim varNRows As Long
    Dim varNColumns As Integer
    Dim varRange1 As Range, varRange2 As Range, _
        varRange3 As Range, varRange4 As Range
    Dim varGreater As Integer
    
    Set varWS = Worksheets("YourSheetName")
    varNRows = varWS.Range("A" & Rows.Count).End(xlUp).Row
    varNColumns = varWS.Cells(1, Columns.Count).End(xlToLeft).Column
    Set varRange2 = varWS.Range("A2:A" & varNRows)
EX:
    On Error GoTo EX2
    For Each varRange1 In varRange2
        varGreater = 0
        Set varRange4 = varWS.Range(Cells(varRange1.Row, 4), _
            Cells(varRange1.Row, varNColumns))
        For Each varRange3 In varRange4
            If varRange3.Value <= 50 Or IsEmpty(varRange3) Or _
                InStr(1, varRange3.Value, " ") Then
            Else
                varGreater = varGreater + 1
                varRange3.Interior.Color = RGB(255, 199, 206)
            End If
        Next
        If Not varGreater > 0 Then
            varWS.Rows(varRange1.Row).Delete
            GoTo EX
        End If
    Next
EX2:

End Sub
 
Upvote 0
Solution
Hi Fluff,

Thank you for the reply. However, I guess my message was not clear as this is not what I am looking for. There is no specific column in which I need to filter the values. My range contains all the columns starting with column D until the last column (dynamic range) and all the rows until the last row (also changing). You can see an example of the data sheet below - the US states (columns) are changing - sometimes there are 40 sometimes only 5, etc. I need to delete entire rows for people for whom there is no value greater than 50 in the whole row - e.g. I need to delete Denis's, Tim's and Tomas's row, but I need to keep Claudia's, Lucie's and Salem's.

Any other ideas, please?

data_sheet.PNG
 
Upvote 0
You can use conditional formatting on col D to show if any cell in that row is greater than 50.
You can then filter col D on colour (using no fill) and delete the visible rows.
This can easily be done & will be quicker than using multiple nested loops.
 
Upvote 0
Hello Dany46,
here is one fresh code,
Try it.
VBA Code:
Option Explicit

Sub KeepGreaterRows()
   
    Dim varWS As Worksheet
    Dim varNRows As Long
    Dim varNColumns As Integer
    Dim varRange1 As Range, varRange2 As Range, _
        varRange3 As Range, varRange4 As Range
    Dim varGreater As Integer
   
    Set varWS = Worksheets("YourSheetName")
    varNRows = varWS.Range("A" & Rows.Count).End(xlUp).Row
    varNColumns = varWS.Cells(1, Columns.Count).End(xlToLeft).Column
    Set varRange2 = varWS.Range("A2:A" & varNRows)
EX:
    On Error GoTo EX2
    For Each varRange1 In varRange2
        varGreater = 0
        Set varRange4 = varWS.Range(Cells(varRange1.Row, 4), _
            Cells(varRange1.Row, varNColumns))
        For Each varRange3 In varRange4
            If varRange3.Value <= 50 Or IsEmpty(varRange3) Or _
                InStr(1, varRange3.Value, " ") Then
            Else
                varGreater = varGreater + 1
                varRange3.Interior.Color = RGB(255, 199, 206)
            End If
        Next
        If Not varGreater > 0 Then
            varWS.Rows(varRange1.Row).Delete
            GoTo EX
        End If
    Next
EX2:

End Sub
Thank you so much, Excel Max! This works perfectly, exactly what I was looking for.
 
Upvote 0
You can use conditional formatting on col D to show if any cell in that row is greater than 50.
You can then filter col D on colour (using no fill) and delete the visible rows.
This can easily be done & will be quicker than using multiple nested loops.
Thank you, Fluff, now I understand, I will try this way too.
 
Upvote 0
I'm glad that code works for you as you expect.
It's not perfect, but can do job.
If something went wrong I've got improved version.
 
Upvote 0
I'm glad that code works for you as you expect.
It's not perfect, but can do job.
If something went wrong I've got improved version.
Hi Excel Max,

I tried to continue this macro with deleting columns that only contain values < 50 or are empty, using a code similar to yours, but for columns instead of rows, but it did not work. I think my main problem is that I am not sure how to correctly set "varRange2" for this scenario. Any advice, please? Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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