Need to fill cells in column next to visible cells in filtered list

[kpal29]

I have a table. The first column called 'Category' is blank, I need to filter by 'BusinessType' column then fill in 'category' column based on various criteria (values in other columns).

My code partly works but these are my problems:
1. The label 'category' is changed along with the other visible cells.

2. When there is no 'Business Type' = "VAS" results in the filter, it fills in all 65K rows with my value instead of just skipping the procedure and moving on to the next filter value.

Here is my code:

Sub categories()
'Fill in Category for Business Type VAS
Range("B1").CurrentRegion.AutoFilter Field:=3, Criteria1:="VAS"
If Range("B1").CurrentRegion.SpecialCells(xlCellTypeVisible).End(xlDown).Address = "$B$65536" Then
Selection.AutoFilter
Else
Range("B1").CurrentRegion.AutoFilter Field:=3, Criteria1:="VAS"
Range("B1").Select
Range("B1").End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.SpecialCells(xlCellTypeVisible).Value = "VAS"
Range("B1").CurrentRegion.AutoFilter

End If

'Fill in Category for Business Type AIR
Range("B1").CurrentRegion.AutoFilter Field:=3, Criteria1:="AIR"
If Range("B1").CurrentRegion.SpecialCells(xlCellTypeVisible).End(xlDown).Address = "$B$65536" Then
Selection.AutoFilter
Else
Range("B1").CurrentRegion.AutoFilter Field:=3, Criteria1:="AIR"
Range("B1").Select
Range("B1").End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.SpecialCells(xlCellTypeVisible).Value = "AIR"
Range("B1").CurrentRegion.AutoFilter
End If

 

[Hcleslie]

Thank you for posting this question. I am just starting to write a macro to use exactly the same logic of using autofilters to identify specific criteria in column and then to populate blank category column.

Did you find answer or solve problem for the label and not matching criteria result?

Charlie

 

[kpal29]

Yes I did. Here is my solution:

First I run a macro that inserts this named range:
ActiveWorkbook.Names.Add Name:="DataRange", RefersToR1C1:="=OFFSET(RawData!R2C1,0,0,COUNTA(RawData!C2)-1,1)"

This selects the the cell in col A next to each row in Column B that is used. Then using this named range:

Sub ICB_PAY_VAS_Categories()
Sheets("RawData").Select
With Range("B1").CurrentRegion
.AutoFilter FIELD:=3, Criteria1:="VAS"
.AutoFilter FIELD:=1, Criteria1:=""
End With
If Range("B1").End(xlDown).Value = "" Then
Range("B1").CurrentRegion.AutoFilter
Else
With Range("DataRange")
NROW = .SpecialCells(xlCellTypeVisible).Row
Range("A" & NROW).Value = "VAS"
Range("A" & NROW).Copy
.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste
End With
Range("B1").CurrentRegion.AutoFilter
End If
End Sub