I am new to VBA macros. I am trying to create a macro that that finds column name "Load Type" applies filter on column value LCL and keep only data rows with LCL and removes rest all data rows. Example Macro should work like

1. Search column named "Load Type"
2. Select/ Filter column Value with LCL
3. Remove all other data other than LCL If column named "Load Type", value <> LCL then entire row delete.

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.

Column N heading is Load type has multiple values LCL, Blanks, BB. I want the macro to keep only data and corrospoding row with column "Load Type" value LCL and remove rest all data.

Desired output is in above image.

My coding image

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.

CodePudding user response：

Delete Data Rows Using 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