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

[Dany46]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about applying conditional formatting to the range & use this formula

=COUNTIF($D2:$AZ2,">50")

You can then filter col D on colour & delete the visible rows.

 

[EXCEL MAX]

Hello Dany46,
here is one fresh code,
Try it.

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

 

[Dany46]

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?

 

[Fluff]

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.

 

[Dany46]

Hello Dany46,
here is one fresh code,
Try it.

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.

 

[Dany46]

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.

 

[Fluff]

I will try this way too.

If you need help just ask.

 

[EXCEL MAX]

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.

 

[Dany46]

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!