jassybun
Member
Hello! I was wondering if would be possible to refer to a List in the second tab of a worksheet instead of including the specific numbers in the VBA as shown below. I have a long list of numbers that I need to put in this code: "If((@=76)+(@=77)+(@=82)+(@=85)" for a different workbook, there are a lot of departments and they are longer in length, 8 or 9 digits. It would be easier to paste them in the second tab, intead of typing, as these numbers will also change.
>>> use code - tags <<<
>>> use code - tags <<<
Code:
Sub jassybun()
Dim Nc As Long, Lr As Long
Dim Ws As Worksheet
With Sheets("Sheet1")
Nc = .Cells(1, Columns.Count).End(xlToLeft).Column + 1
Lr = .Range("A" & Rows.Count).End(xlUp).Row
With .Range(.Cells(1, Nc), .Cells(Lr, Nc))
.Value = Evaluate(Replace("If((@=76)+(@=77)+(@=82)+(@=85),true,"""")", "@", .Offset(, -1).Address))
End With
.Range("A1", .Cells(Lr, Nc)).AutoFilter Nc, ""
.AutoFilter.Range.Offset(1).EntireRow.Delete
.AutoFilterMode = False
.Columns(Nc).ClearContents
End With
End Sub
Attachments
Last edited by a moderator: