How to delete lines from a spreadsheet after performing a filter using VBA?

Asked

Viewed 95 times

3

I am having trouble deleting lines resulting from a text filter in a table. I recorded a macro that resulted in the following code:

ActiveSheet.Range("$A$1:$K$161").AutoFilter Field:=2, Criteria1:= _  
  "=*EXT-11198*", Operator:=xlAnd  
Rows("30:200").Select  
Selection.Delete Shift:=xlUp  
ActiveSheet.Range("$A$1:$K$139").AutoFilter Field:=2"

It only worked for that specific table, when I apply it to another table with different number of records or the macro deletes more or less records.

I believe that inserting a routine that counts the resulting lines of the filter and deletes only those lines solves the problem.

excel vba excel-vba

  • You are applying the filter up to line 161, so of course, if another sheet has a larger size, the filter will not affect it (as you said yourself). Also, you are deleting from 30 to 200. What is the parameter to delete this range of lines?

  • Calculating the last line is easy, but the second part depends on why you want to specifically delete filtered lines 30 to 200.

  • The selection up to the 200 line was random, what needs is to select all lines that contain a code, in this case "EXT-11198" and delete them before processing the rest of the macro.

1 answer

1

The problem is that the range you are trying to delete is variable, so before deleting you need to figure out which are these lines using the property UsedRange.

' aplicar filtro
ActiveSheet.Range("$A$1:$K$161").AutoFilter Field:=2, Criteria1:= _  
  "=*EXT-11198*", Operator:=xlAnd  

' pegar a área filtrada exceto o cabeçalho
Set area_filtrada = ActiveSheet.UsedRange.Offset(1, 0)

' excluir a área filtrada
area_filtrada.Delete Shift:=xlUp

' exibir todos os dados
ActiveSheet.AutoFilter.ShowAllData

Browser other questions tagged excel vba excel-vba

You are not signed in. Login or sign up in order to post.