0

I created a macro in a worksheet with 1200 rows. This number will change weekly. This week, I have 970 rows. When I ran the macro, which has some IF functions built in, it gave me "FALSE" for the ~230 rows difference (when I recorded the Macro, I did a "fill down" on the columns.

This is not a duplicate because I cannot find any article that tells me where to put the code in VBA.

How can I ensure it only works with data-filled cells?

I checked these resources:

Fill down a row on VBA

Error in finding last used cell in VBA

I think lastUsedRow might be my best option, but I don't know where to put it in VBA or if it even is my best option.

Here's the snippet where it autofills: 

Range("J2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(RC[2]=""Marketing CG2"",""Call Group 2"",IF(RC[2]=""Marketing CG3"",""Call Group 3"",IF(RC[2]=""Marketing CG1"",""Call Group 1"",IF(RC[2]=""Marketing"",""EDIT SKILL""))))"
Range("I2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(SEARCH(""Nurture"",RC[-1])),""New Producer Nurture""," & _
"IF(ISNUMBER(SEARCH(""WB FU"",RC[-1])),""Event Follow Up""," & _
"IF(ISNUMBER(SEARCH(""WS FU"",RC[-1])),""Event Follow Up""," & _
"IF(ISNUMBER(SEARCH(""WS REM"",RC[-1])),""Event Promotion""," & _
"IF(ISNUMBER(SEARCH(""Marketing OB"",RC[-1])),""General Marketing""," & _
"IF(ISNUMBER(SEARCH(""Marketing Direct to Agent"",RC[-1])),""Marketing""," & _
"IF(ISNUMBER(SEARCH(""Info Email Response"",RC[-1])),""Lead Outreach""," & _
"IF(ISNUMBER(SEARCH(""Marketing IB"",RC[-1])),""General Marketing""," & _
"IF(ISNUMBER(SEARCH(""ZProgramsMatch"",RC[-1])),""Lead Outreach""," & _
"IF(ISNUMBER(SEARCH(""Registration Support"",RC[-1])),""General Marketing""," & _
"IF(ISNUMBER(SEARCH(""Complete Market Lead"",RC[-1])),""Lead Outreach""," & _
"IF(ISNUMBER(SEARCH(""Canada"",RC[-1])),""General Marketing""," & _
"IF(ISNUMBER(SEARCH(""Web Contact Form Outreach"",RC[-1])),""Lead Outreach""," & _
"IF(ISNUMBER(SEARCH(""General Product Inquiry"",RC[-1])),""General Marketing""))))))))))))))"
Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I1070")
Range("I2:I1070").Select
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J1070")
Range("J2:J1070").Select
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
James Winthrop
  • 33
  • 7
  • is this the actual VBA code in your worksheet? – Kathara Nov 28 '17 at 13:22
  • Yes, partially. I can include the entire code, if that would help. – James Winthrop Nov 28 '17 at 13:23
  • is there any column that is always filled? – Kathara Nov 28 '17 at 13:24
  • Yes, any column between A and AI will be filled with data in a changing number of rows. Columns I, J, W are those with autofill (they are created and filled during the macro running) – James Winthrop Nov 28 '17 at 13:25
  • Possible duplicate of [Last non-empty cell in a column](https://stackoverflow.com/questions/5441885/last-non-empty-cell-in-a-column) – ashleedawg Nov 28 '17 at 13:28
  • So lastRowUsed is actually the best option. you can store it in a variable and use the variable to set the ranges. And you should definitely recode: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba ; https://www.businessprogrammer.com/power-excel-vba-secret-avoid-using-select/ ; http://www.makeuseof.com/tag/4-mistakes-can-avoid-programming-excel-macros-vba/ – Kathara Nov 28 '17 at 13:29

1 Answers1

1

put dim lrow as integer at start of code use this after if formula ends

lrow = Range("I" & Rows.Count).End(xlUp).Row

Range("I2").Select
Selection.AutoFill Destination:=Range("I2:I" & lrow)
lrow = Range("J" & Rows.Count).End(xlUp).Row
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J" & lrow)
Digvijay
  • 406
  • 3
  • 12
  • I tried this, but it returned an error 1004: Autofill Method of Range class failed at this line: `Selection.AutoFill Destination:=Range("I2:I" & lrow)` – James Winthrop Nov 28 '17 at 13:36
  • `Sub CRF() ' ' CRF Macro ' ' Dim lrow As Integer ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 Selection.AutoFilter` is how I placed it at the beginning of the code. – James Winthrop Nov 28 '17 at 13:37