0

I am trying to copy the data in the cells underneath columns A and B if cell E is blank. I applied a filter to look at only the data with cell E empty however i am getting errors in my code. This is what i have

sht10.Range("E2").AutoFilter Field:=5, Criteria1:="="

I did already identify all my variables this is part of a larger code.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Toriwein
  • 1
  • 2
  • 2
    There is no `Field:=5` for a single column `Range`. > `sht10.Range("E2").AutoFilter Field:=1, Criteria1:="="` – JvdV Jan 22 '20 at 16:04
  • This didn't work. It only displayed all empty rows not the ones with just cell E empty. – Toriwein Jan 22 '20 at 16:20
  • `sht10.Range("E2:E" & LastRow).AutoFilter Field:=1, Criteria1:="="` You can get the last row as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) Another example can be seen [HERE](https://stackoverflow.com/questions/11317172/delete-row-based-on-condition/11317372#11317372) – Siddharth Rout Jan 22 '20 at 16:24
  • Still didnt work. Only displaying completely empty rows. Need the ones with just cell E empty – Toriwein Jan 22 '20 at 16:31
  • Have you tried to incorporate the rest of your range > `sht10.Range("A2:E" & LastRow).AutoFilter Field:=5, Criteria1:="="` – JvdV Jan 22 '20 at 16:36
  • I think I know why its not working. There is a formula in the cells I am looking for But not a displayed string – Toriwein Jan 22 '20 at 16:36
  • Filter is only applied on column `E`. It shouldn't matter whats in the other columns – Zac Jan 22 '20 at 16:58
  • it still is not working. – Toriwein Jan 22 '20 at 19:18

1 Answers1

1

Before:

enter image description here

Some code that "works":

Sub ytrewq()

    Dim sht10 As Worksheet
    Set sht10 = ActiveSheet

    sht10.Range("E2").AutoFilter Field:=1, Criteria1:="="

End Sub

and after:

enter image description here

By "works" I do not mean it will do anything useful, only that it will SOMETHING without falling over dead.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99