我是 VBA 宏的新手。我正在嘗試創建一個宏來查找列名“加載型別”對列值 LCL 應用過濾器，并僅使用 LCL 保留資料行并洗掉其余所有資料行。示例宏應該像
- 名為“負載型別”的搜索列
- 使用 LCL 選擇/過濾列值
- 洗掉 LCL 以外的所有其他資料 如果列名為“加載型別”，值 <> LCL，則洗掉整行。
我希望宏只保留名為“加載型別”列中值為 LCL 的資料，并洗掉其余所有資料，即使有空白，如果加載型別為空白，它也應該洗掉整行。
N 列標題是負載型別，具有多個值 LCL、Blanks、BB。我希望宏只保留資料和帶有列“加載型別”值 LCL 的腐蝕行，并洗掉其余所有資料。
所需的輸出在上圖中。
我的編碼影像
I tried coading like this but its says variable not defined I am confused of do i fix this.
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
I want the macro to keep only data with value LCL in column named Load Type and remove rest all data even if there is blank it should remove the entire row if load type is blank.
uj5u.com熱心網友回復：
洗掉資料行使用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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/452341.html