'How to Run a Macro only in rows with the date after March 1st?

I have a very big database (more than 450k rows and 75 columns) and to make it lighter and faster I created a Macro. I'm still a newbie to VBA, so it would be great if you could help me with this situation:

My code is already working for the whole thing, but now I need it to not run on rows in which column B contains a date older than March 1st 2022. Is it feasible for such a heavy weight (running on MacBook)?

Also, if you guys have any tip on how I could make the code any faster it would be awesome. Thank you all!

Sub Calculos_BD()
'
' Cálculos BD Macro
'

'
    Application.ScreenUpdating = False
    

    Sheets("InterExpenses_BD").Select
    
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:AM2"), Type:=xlFillDefault
    

    Range("AN2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AO2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AP2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AQ2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AR2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AS2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AT2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
    
    Range("AU2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-36],'Completo - CC_Dados'!C[-46]:C[-38],9,0)"
    
    Range("AV2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-37],'Completo - CC_Dados'!C[-47]:C[-45],3,0)"
    
    Range("AW2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Completo - CC_Dados'!C[-46]:C[-44],3,0)"
    
    Range("AX2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],2)"
    
    Range("AY2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'Completo - CC_Dados'!C[-48]:C[-46],3,0)"
    
    Range("AZ2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>2,LEFT(RC48,5),"""")"
    
    Range("BA2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BB2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>5,LEFT(RC[-6],8),"""")"
    
    Range("BC2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BD2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(LEN(RC48)>8,RC3=""Investimentos"",RC47=""Institucional_Projetos""),LEFT(RC48,12),IF(AND(LEN(RC48)>8,RC3=""Investimentos"",RC47<>""Institucional_Projetos""),LEFT(RC48,11),IF(AND(LEN(RC48)>8,RC3<>""Investimentos""),LEFT(RC48,11),"""")))"
    
    Range("BE2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-52]=""Investimentos"","""",IF(LEN(RC48)>11,LEFT(RC48,14),""""))"
    
    Range("BG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))="""",RC[-1],IFERROR(VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0),RC[-1]))"
    
    Range("BH2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>14,LEFT(RC48,17),"""")"
    
    Range("BI2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BJ2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>17,LEFT(RC48,20),"""")"
    
    Range("BK2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BL2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>20,LEFT(RC48,23),"""")"
    
    Range("BM2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BN2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>23,LEFT(RC48,26),"""")"
    
    Range("BO2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BP2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>26,LEFT(RC48,29),"""")"
    
    Range("BQ2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BR2").Select
    ActiveCell.FormulaR1C1 = "=IF(LEN(RC48)>29,LEFT(RC48,32),"""")"
    
    Range("BS2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    Range("BT2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-61],'Completo - CC_Dados'!C[-71]:C[-20],49,0)"
    
    Range("BU2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-62],'Completo - CC_Dados'!C[-72]:C[-21],50,0)"
    
    Range("BW2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-64],'Completo - CC_Dados'!C[-74]:C[-23],52,0)"
    
    Range("BX2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-65],'Completo - CC_Dados'!C1:C11,11,0)"
        
    
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("L2:BU2").AutoFill Range("L2:BU2").Resize(LastRow)
    Range("BW2:BX2").AutoFill Range("BW2:BX2").Resize(LastRow)
    
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[3]),MONTH(RC[3]),1)"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[8],'Completo - CC_Dados'!C[-2]:C[9],12,0)"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)<>""GRUPO DRE"",VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0),IF(AND(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""IDTVM_Asset"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS"",IF(AND(VLOOKUP(RC[2],PLANO_DE_CON" & _
        "TAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""Inter Seguros"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS"",IF(AND(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""Marketplace"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS""" & _
        ",IF(AND(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""DLM"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS"",""GRUPO DRE"")))))" & _
        ""

    Range("B2:D2").AutoFill Range("B2:D2").Resize(LastRow)
    
    Application.ScreenUpdating = True
 
End Sub

I tried looking up a way to do this but failed. I know it would be some kind of conditional, but have no idea on how to do it the cleanest and fastest way.

EDIT 1

Made more research and came to one solution, removed all the selects in the loop as suggested in comments but it's taking too long to run.

If you guys could help me with more advanced techniques to do the same thing, I would be really grateful.

Here's the new code:

Sub Calculos_BD_Criterio_Mes() '

Application.ScreenUpdating = False
Application.DisplayStatusBar = False

Dim linha As Long
Dim ws As Worksheet
Dim rng As Range

Set ws = Worksheets("InterExpenses_BD")
Set rng = ws.Cells(1, 1)

Range("B2").Select
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[3]),MONTH(RC[3]),1)"
Range("C2").Select
ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[8],'Completo - CC_Dados'!C[-2]:C[9],12,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = _
    "=IF(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)<>""GRUPO DRE"",VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0),IF(AND(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""IDTVM_Asset"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS"",IF(AND(VLOOKUP(RC[2],PLANO_DE_CON" & _
    "TAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""Inter Seguros"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS"",IF(AND(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""Marketplace"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS""" & _
    ",IF(AND(VLOOKUP(RC[2],PLANO_DE_CONTAS_Analítico!C[-3]:C[13],17,0)=""GRUPO DRE"",(VLOOKUP(RC[7],'Completo - CC_Dados'!C[-3]:C[5],9,0)=""DLM"")),""OUTRAS DESPESAS OPERACIONAIS/NÃO OPERACIONAIS"",""GRUPO DRE"")))))" & _
    ""
Range("BY2").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-75])"

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:D2").AutoFill Range("B2:D2").Resize(LastRow)
Range("BY2").AutoFill Range("BY2").Resize(LastRow)

Application.Calculation = xlCalculationManual
Application.EnableEvents = False

linha = 2

While Cells(linha, 1) <> ""
    
    If Cells(linha, 77) >= 3 Then
' Fórmulas de L até AT
        Range(Cells(linha, 12), Cells(linha, 46)).FormulaR1C1 = "=INDEX('Completo - CC_Dados'!R2C14:R1698C48,MATCH(InterExpenses_BD!RC11,'Completo - CC_Dados'!R2C1:R1698C1,0),MATCH(InterExpenses_BD!R1C,'Completo - CC_Dados'!R1C14:R1C48,0))*InterExpenses_BD!RC10"
        
    ' Fórmula AU
        Cells(linha, 47).FormulaR1C1 = "=VLOOKUP(RC[-36],'Completo - CC_Dados'!C[-46]:C[-38],9,0)"
    
    ' Fórmula AV
        Cells(linha, 48).FormulaR1C1 = "=VLOOKUP(RC[-37],'Completo - CC_Dados'!C[-47]:C[-45],3,0)"
    
    ' Fórmula AW
        Cells(linha, 49).FormulaR1C1 = "=VLOOKUP(RC[-1],'Completo - CC_Dados'!C[-46]:C[-44],3,0)"
    
    ' Fórmula AX
        Cells(linha, 50).FormulaR1C1 = "=LEFT(RC[-2],2)"
    
    ' Fórmula AY
        Cells(linha, 51).FormulaR1C1 = "=VLOOKUP(RC[-1],'Completo - CC_Dados'!C[-48]:C[-46],3,0)"
        
    ' Fórmula AZ
        Cells(linha, 52).FormulaR1C1 = "=IF(LEN(RC48)>2,LEFT(RC48,5),"""")"
    
    ' Fórmula BA
        Cells(linha, 53).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
        
    ' Fórmula BB
        Cells(linha, 54).FormulaR1C1 = "=IF(LEN(RC48)>5,LEFT(RC[-6],8),"""")"
    
    ' Fórmula BC
        Cells(linha, 55).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BD
        Cells(linha, 56).FormulaR1C1 = "=IF(AND(LEN(RC48)>8,RC3=""Investimentos"",RC47=""Institucional_Projetos""),LEFT(RC48,12),IF(AND(LEN(RC48)>8,RC3=""Investimentos"",RC47<>""Institucional_Projetos""),LEFT(RC48,11),IF(AND(LEN(RC48)>8,RC3<>""Investimentos""),LEFT(RC48,11),"""")))"

    ' Fórmula BE
        Cells(linha, 57).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"

    ' Fórmula BF
        Cells(linha, 58).FormulaR1C1 = "=IF(RC[-52]=""Investimentos"","""",IF(LEN(RC48)>11,LEFT(RC48,14),""""))"
        
    ' Fórmula BG
        Cells(linha, 59).FormulaR1C1 = "=IF(IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))="""",RC[-1],IFERROR(VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0),RC[-1]))"
        
    ' Fórmula BH
        Cells(linha, 60).FormulaR1C1 = "=IF(LEN(RC48)>14,LEFT(RC48,17),"""")"
    
    ' Fórmula BI
        Cells(linha, 61).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BJ
        Cells(linha, 62).FormulaR1C1 = "=IF(LEN(RC48)>17,LEFT(RC48,20),"""")"
    
    ' Fórmula BK
        Cells(linha, 63).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BL
        Cells(linha, 64).FormulaR1C1 = "=IF(LEN(RC48)>20,LEFT(RC48,23),"""")"
    
    ' Fórmula BM
        Cells(linha, 65).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BN
        Cells(linha, 66).FormulaR1C1 = "=IF(LEN(RC48)>23,LEFT(RC48,26),"""")"
    
    ' Fórmula BO
        Cells(linha, 67).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BP
        Cells(linha, 68).FormulaR1C1 = "=IF(LEN(RC48)>26,LEFT(RC48,29),"""")"
    
    ' Fórmula BQ
        Cells(linha, 69).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BR
        Cells(linha, 70).FormulaR1C1 = "=IF(LEN(RC48)>29,LEFT(RC48,32),"""")"
    
    ' Fórmula BS
        Cells(linha, 71).FormulaR1C1 = "=IF(RC[-1]="""","""",VLOOKUP(RC[-1],'Completo - CC_Dados'!C3:C5,3,0))"
    
    ' Fórmula BT
        Cells(linha, 72).FormulaR1C1 = "=VLOOKUP(RC[-61],'Completo - CC_Dados'!C[-71]:C[-20],49,0)"
    
    ' Fórmula BU
        Cells(linha, 73).FormulaR1C1 = "=VLOOKUP(RC[-62],'Completo - CC_Dados'!C[-72]:C[-21],50,0)"
    
    ' Fórmula BX
        Cells(linha, 76).FormulaR1C1 = "=VLOOKUP(RC[-65],'Completo - CC_Dados'!C1:C11,11,0)"
    
    End If
        
        linha = linha + 1

Wend
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True

End Sub

excelvba


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions

Reading Excel file with Selenium WebDriver C# and web form with values

How to update an existing row or table of an existing xlsx file using pyexcel for python

Equivalent C# Function For Excels Norm.S.Inv Function

I keep assigning a value to my variable but it stays empty

Excel RTD (Real Time Data) client other than Excel?

Create table from data on a worksheet with specific name and add column

Is it possible to use 'Microsoft.ACE.OLEDB.12.0' provider without installation?

Using VBA to query a SQL Server table in Excel

How to stop scroll bar auto jump in vba

If IsEmpty then go to column A but in same row and extract that data

How can I use a for each loop on an array?

VBA - worksheet object is hidden

Outlook 2010 GAL with Excel VBA

how to read a value from Column Name in Excel in Java

Replace cell value with image