0

I'm trying to create a Match macro that compares two lists and gives me the cells that present in only one of the lists. The cells are then copied to another sheet where the cells are counted. However, the blank cells are also being copied and I don't know why. The below is what I have:

Sub Macro_do_Match()

Dim CopyrangeB As String
Dim lRowB As Integer
Dim fRowB As Integer
Dim CopyrangeD As String
Dim lRowD As Integer
Dim fRowD As Integer
Dim rng As Range

' Defines range for column B

lRowB = Cells(Rows.Count, 1).End(xlUp).Row
fRowB = 2

Let CopyrangeB = "B" & fRowB & ":" & "B" & lRowB

' "macro"

    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(NOT(ISERROR(MATCH(C[-1],C[1],0)))=FALSE,C[-1], """")"
    Range("B2").Select
    Selection.AutoFill Destination:=Range(CopyrangeB)

' Defines range for column D

lRowD = Cells(Rows.Count, 3).End(xlUp).Row
fRowD = 2

Let CopyrangeD = "D" & fRowD & ":" & "D" & lRowD

' "macro"

    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(NOT(ISERROR(MATCH(C[-1],C[-3],0)))=FALSE,C[-1], """")"
    Range("D2").Select
    Selection.AutoFill Destination:=Range(CopyrangeD)

'Copy and paste B

    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Final Results").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'Copy and paste D

    Sheets("Insert Lists").Select
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Final Results").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
mathplas
  • 1
  • 1
    Try to implement `Trim()` - https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.trim – Vityata Feb 11 '19 at 12:36
  • 1
    Please note that **all** row counting variables must be of type `Long` because Excel has more rows than `Integer` can handle: `Dim lRowB As Long`. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Feb 11 '19 at 12:36
  • Probably Excel is unable to guess Formula Blanks and therefore it is copying. The other thing (untested) comes to mind is `SkipBlanks :=False` shall be `SkipBlanks :=True`. – shrivallabha.redij Feb 11 '19 at 12:41
  • Unrelated, but instead of `NOT(ISERROR(MATCH(C[-1],C[-3],0)))=FALSE`, you could just use `ISERROR(MATCH(C[-1],C[-3],0))` – jsheeran Feb 11 '19 at 13:52

0 Answers0