在基于过滤器删除时请求动态范围解决方案 [英] Request for dynamic range solution when deleting based on a filter

问题描述

我目前正在做一个宏来自动执行我每天在Excel中所做的一些工作。然而，由于这是我第一次遇到Visual Basic和Excel中的宏，所以我一直在努力实现基于过滤器结果的动态范围选择。

基本上，想法是在标头上应用过滤器后，我删除一系列行。由于行范围的变化，我需要代码根据过滤器结果动态选择范围。要给出一个实际的例子，这是一个我应用的过滤器：

 列（J：J）。EntireColumn。 AutoFit 
 ActiveSheet.Range（$ A $ 4：$ AQ& LastRow）.AutoFilter字段：= 10，Criteria1：==
行（34:34）。 $ b Range（Selection，Selection.End（xlDown））。选择
 Selection.Delete Shift：= xlUp 
 ActiveSheet.Range（$ A $ 4：$ AQ& LastRow）.AutoFilter字段： = 10 
列（J：J）。选择
 Selection.EntireColumn.Hidden = True 
  

问题在于行（34:34），选择，因为这些行会每天都会更改。

我已经在网站上做了一些阅读，我已经尝试了几个关于SpecialCell功能的建议，但是这些建议目前不适用于我（我可能会做错事）。

$ b $我想，有一个简单的解决方案，所以我希望你们中的一个能指向正确的方向？这将帮助我很多。

我希望我有一定的意义。

谢谢你提前！

解决方案

尝试这一个：

  With ActiveSheet.Range（A4：AQ& lastrow）
 .AutoFilter字段：= 10，Criteria1：==
'只有可见的行将被删除
 .Offset（1）.Resize（.Rows.Count  -  1）.EntireRow.Delete Shift：= xlUp 
 .AutoFilter字段：= 10 
结束与
  

I'm currently working on making a macro to automate some work I have to do in Excel on a daily basis. However, since this is my first encounter with Visual Basic and Macros in Excel in general, I'm struggling to implement dynamic range selection based on filter results.

Basically, the idea is that I delete a range of rows after applying a filter on a header. Since this range of rows changes, I need the code to dynamically pick the range based on the filter result. To give a practical example, this is one filter I'm applying:

Columns("J:J").EntireColumn.AutoFit
ActiveSheet.Range("$A$4:$AQ" & LastRow).AutoFilter Field:=10, Criteria1:="="
Rows("34:34").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$4:$AQ" & LastRow).AutoFilter Field:=10
Columns("J:J").Select
Selection.EntireColumn.Hidden = True

The issue lies with "Rows("34:34").Select", since these rows would change from day to day.

I've done some reading around the website, and I have tried several suggestions involving the SpecialCell function, however, these aren't currently working for me (I'm likely doing something wrong).

I imagine there to be a simple solution to this, so I'm hoping one of you would point me in the right direction? It would be help me a lot.

I hope I've managed to make some sense.

Thank you in advance!

解决方案

Try this one:

With ActiveSheet.Range("A4:AQ" & lastrow)
    .AutoFilter Field:=10, Criteria1:="="
    'get all rows except header. Only visible rows would be deleted
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete Shift:=xlUp
    .AutoFilter Field:=10
End With

这篇关于在基于过滤器删除时请求动态范围解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！