Short macro with IFs and range selection

[Mr2017]

Hi

I'd like to write some code which will

1) apply an auto-filter to the first row of data that's been put into a sheet (but only if an auto-filter has not already been applied)
2) Checks if the word "Test" is in cell D1
2) If the above is true, then it would filter by the blanks in column D
3) then remove all rows that have a blank value in column D

I'd then like to add an IF statement that says if the word "Test is in cell E1, instead, then it

4) filters by the blanks in column E
5) removes all rows with a blank value in column E.

I've recorded a simple version of the above (without the IF statements) and the code is below - however, this wouldn't work if the raw data applied to the spreadsheet had a greater number of rows.

Does anyone know how to modify the code below so that it applies what is described in points 1 to 5 above? TIA

Sub remove_record()


'remove_record Macro


Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$U$3937").AutoFilter Field:=4, Criteria1:="="
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$U$3935").AutoFilter Field:=4
Range("A1").Select
End Sub

 

[Fluff]

Re point 1.
What do you want to happen if there is already an autofilter in place?

 

[Mr2017]

Hi Fluff

Thanks for the prompt response.

Good question.

If there already is an auto filter applied to row 1, then I’d want to proceed to step 2.

I’ve noticed that I accidentally listed the number 2 twice in the original question, so there should actually be 6 steps. Apologies for any confusion.

Let me know if you have any other questions?

 

[Fluff]

How about

Sub FilterDeleteBlanks()
   Dim Flg As Boolean
   With ActiveSheet
      Flg = .AutoFilterMode
      If .Range("D1").Value = "Test" Then
         .Range("A1:U1").AutoFilter 4, "="
         .AutoFilter.Range.Offset(1).EntireRow.Delete
      End If
      If .Range("E1").Value = "Test" Then
         .Range("A1:U1").AutoFilter 4
         .Range("A1:U1").AutoFilter 5, "="
         .AutoFilter.Range.Offset(1).EntireRow.Delete
      End If
      If Flg Then .Range("A1:U1").AutoFilter 5 Else .AutoFilterMode = False
   End With
End Sub

 

[Mr2017]

Ok, thanks Fluff - that worked!

 

[Fluff]

You're welcome

 

[Mr2017]

Do you know how I can amend it to incorporate the range regardless of how many columns there are?

At the moment, the selection goes up to column U, but I’d like to use the endxldown and endxlright functions to cover the whole range, in case there is data that goes up to column V or W, for example.

TIA

 

[Fluff]

As long as you have a header for each column you can just use

Range("A1").Autofilter

 

[Mr2017]

Ok, great!

Thanks - yes, every column in the data has a heading.