我是 VBA 宏的新手。我正在尝试创建一个宏来查找列名“加载类型”对列值 LCL 应用过滤器,并仅使用 LCL 保留数据行并删除其余所有数据行。示例宏应该像
我希望宏只保留名为“加载类型”列中值为 LCL 的数据,并删除其余所有数据,即使有空白,如果加载类型为空白,它也应该删除整行。
N 列标题是负载类型,具有多个值 LCL、Blanks、BB。我希望宏只保留数据和带有列“加载类型”值 LCL 的腐蚀行,并删除其余所有数据。
所需的输出在上图中。
我的编码图像
我尝试过这样的编码,但它说变量未定义我很困惑我是否解决了这个问题。
Sub SortLCL_Concat()
Dim wb As Workbook
Dim sRng As Range
Dim fRng As Range
Dim cel As Range
Dim tRow As Long
Dim fCol As Long
Set wb = ThisWorkbook
Set fRng = ActiveWorkbook.Worksheets("Main")
fCol = fRng.Column
tRow = ActiveWorkbook.Worksheets("Main").Cells(Rows.Count, 1).End(xlUp).Row
With ActiveWorkbook.Worksheets("Main")
For tRow = .Rows.Count To 2 Step -1
If .Cells(tRow, fCol).Value <> LCL Then .Rows(tRow).Delete
Next tRow
End With
End Sub
我希望宏只保留名为“加载类型”列中值为 LCL 的数据,并删除其余所有数据,即使有空白,如果加载类型为空白,它也应该删除整行。
AutoFilter
Option Explicit
Sub SortLCL_Concat()
Const wsName As String = "Main"
Const FilterColumnTitle As String = "Load Type"
Const FilterCriteria As String = "<>LCL"
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
If ws.FilterMode Then ws.ShowAllData
Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
Dim fCol As Long: fCol = Application.Match(FilterColumnTitle, rg.Rows(1), 0)
Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1)
rg.AutoFilter fCol, FilterCriteria
Dim vdrg As Range
On Error Resume Next
Set vdrg = drg.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
ws.AutoFilterMode = False
If vdrg Is Nothing Then Exit Sub
vdrg.Delete xlShiftUp
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句