0

A VBA beginner here. I'm trying to write a code that will add a new column, and using a loop to fill the values in the new column, based on information from other columns. E.g., if a cell in Column 'AL' has "Germany" on it, it will also write "Germany on the new column. The problem is, that the worksheet includes more than 50k rows and 50 columns, and it takes a few minutes for the loop to be completed. So what I did, is to first filter some values on column 'AL' ("Germany" and "UK"..), run a loop, and then run the loop on another filtered column. But it still takes ages for the two loop to finish.

I wonder if there's a way to improve the process? I think that even though I filtered it, the loop still runs on all values, not just the visible ones.

Sub Macro6()


      'Filter Germany,UK, EMEA
    Range("AL2").EntireColumn.Insert
    Range("A1").CurrentRegion.AutoFilter 42, Array("Germany", "UK","EMEA")        ''''''
    Range("AQ3").Select
       'Create a loop to insert values
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = "Germany" Then
    ActiveCell.Offset(0, -5).Value = "Germany"
    ElseIf ActiveCell.Value = "UK" Then
    ActiveCell.Offset(0, -5).Value = "UKI"
    ActiveCell.Offset(0, -5).Value = "EMEA"
    End If
    ActiveCell.Offset(1, 0).Select
    Loop


End Sub


Sub Macro7()

        'Filter APAC, Americas and EMEA
    If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
    Range("A1").CurrentRegion.AutoFilter 38, Array("Americas", "Asia/Pacific", "EMEA")
    Range("AM3").Select
        'Create a loop to insert values
    Do Until ActiveCell.Value = ""
    If ActiveCell.Value = "Americas" Then
    ActiveCell.Offset(0, -1).Value = "Americas"
    ElseIf ActiveCell.Value = "EMEA" Then
    ActiveCell.Offset(0, -1).Value = "EMEA"
    ElseIf ActiveCell.Value = "Asia/Pacific" Then
    ActiveCell.Offset(0, -1).Value = "APAC"
    End If
    ActiveCell.Offset(1, 0).Select
    Loop

End Sub
Cameron Critchlow
  • 1,814
  • 1
  • 4
  • 14
TheKid90210
  • 1
  • 2
    Is there a reason you're using VBA and not a simple `XLOOKUP` or `INDEX/MATCH` or `VLOOKUP` formula? – BigBen Jan 04 '23 at 18:35
  • 2
    Is the missing quote after EMEA in your code? Or just this post? – braX Jan 04 '23 at 18:37
  • 2
    In general, you want to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code – cybernetic.nomad Jan 04 '23 at 18:39
  • @braX, the quote is missing just here. – TheKid90210 Jan 04 '23 at 19:51
  • @BigBen, I just started learning VBA, so I'm trying to practice simple tasks. But tbh, I'm not sure how to perform this task with one of the formulas suggested. – TheKid90210 Jan 04 '23 at 19:51
  • If it's OK with you that the code doesn't use filter method, maybe you want to try something like this : make an array variable with the country name you want ---> `arr = Array("Germany", "UK", "EMEA")`. After `Range("AL2").EntireColumn.Insert` is executed, please note in what column is the COUNTRY column now. Example case : before insert a new column, the COUNTRY column is column AO (41) then after insert a new column the COUNTRY column is column AP (42). (continue) – karma Jan 05 '23 at 07:01
  • `For Each el In arr` ... `With Columns(42)` ... `.Replace el, True, xlWhole, , False, , False, False` ... `.SpecialCells(xlConstants, xlLogical).Offset(0, -4).Value = el` ... `.Replace True, el, xlWhole, , False, , False, False` ... `end with` ... `next`. So, on each looped element (el variable) in arr variable, it get the range of cells in column 42 which has the el value, then fill that range offset -4 (which is the new inserted column) with the el value. – karma Jan 05 '23 at 07:06

0 Answers0