Recording a macro is hardcoding the number of rows into a formula, how to replace with LRow or equivalent to make macro dynamically reusable?

Question

I'm trying to create an Excel VBA Macro that will replicate the following steps:

Insert a blank column in F:F- Success!
Label F1 "Enrollments" - Success!
Insert the following code into F2: =IF(E2="","",IF(COUNTIF(E2:E15735,E2)=1,E2,"")) (This formula makes it so that duplicate enrollments only show once in the column, allowing for cleanly pivoting the data (after first clearing the formula from the blank cells... I'm not that far in the VBA script yet...))
Copy the contents down (double-click on the small black cross at the bottom right of F2)

This generates the following VBA code (which can possibly be cleaned up or trimmed, I haven't looked at that yet):

Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Enrollments"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(COUNTIF(RC[-1]:R[15733]C[-1],RC[-1])=1,RC[-1],""""))"
    Range("F3").Select
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F15735")
    Range("F2:F15735").Select

The question I have is this: I want to reuse this code every time we pull a new enrollment report. To that end, I don't need it to go to row 15733 every time, I need it to dynamically compare to the last row (which I've seen as "F" & LRow but I can't figure out how to insert that into the IF(COUNTIF) statement without throwing an immediate error in the debugger... which (to my untrained eyes) isn't giving me any real feedback on how to fix whatever error I'm creating trying to shoe-horn LRow into the formula.

score 0 · Accepted Answer · answered Aug 01 '17 at 20:13

Add:

Dim sht As Worksheet
Dim lastrow as Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "F").End(xlUp).Row

Then switch out 15735 for lastrow:

Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Enrollments"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(COUNTIF(RC[-1]:R[" & lastrow & "]C[-1],RC[-1])=1,RC[-1],""""))"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F" & lastrow)
    Range("F2:F5" & lastrow).Select

Also, try to avoid using Select when you can. See here

Worked like a charm, and yes, I should have been more explicit when I mentioned not having trimming the code yet, that the extraneous selections and whatnot are definitely on the chopping block! Thanks! — Dustin Kreidler, Aug 02 '17 at 18:23

score 0 · Answer 2 · answered Aug 01 '17 at 20:20

0

=IF(E2="","",IF(COUNTIF(E2:E15735,E2)=1,E2,""))

change to

=IF(E2="","",IF(COUNTIF(E:E,E2)=1,E2,""))

answered Aug 01 '17 at 20:20

xgg

159
6

Recording a macro is hardcoding the number of rows into a formula, how to replace with LRow or equivalent to make macro dynamically reusable?

2 Answers2