0

I'm new to VBA code. Attempting to Fill values for filtered range in a particular column ("M") and below code is attempted

Dim prg_type As String
Dim header_name As String
Dim MyColumnNumber As Integer
Dim aCell As Range
Dim i As Integer
Dim c As Excel.Range

For i = 1 To 7
    header_name = ActiveWorkbook.Worksheets("Execution").Cells(i + 3, 2).Text
    prg_type = ActiveWorkbook.Worksheets("Execution").Cells(i + 3, 3).Text
    
    Sheets("Raw Data").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFilter
    
    Set aCell = ActiveWorkbook.Worksheets("Raw Data").Rows(1).Find(What:="program_type", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    MyColumnNumber = aCell.Column
    ActiveSheet. Range("$A$1:$M$300000").AutoFilter Field:=MyColumnNumber, Criteria1:="="
    
    Set aCell = ActiveWorkbook.Worksheets("Raw Data").Rows(1).Find(What:=header_name, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    MyColumnNumber = aCell.Column
    ActiveSheet.Range("$A$1:$M$300000").AutoFilter Field:=MyColumnNumber, Criteria1:="1"

    For Each c In Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        If c.Offset(, -1).Value = vbNullString Then Exit For
        c.Value = prg_type
    Next

 
    ActiveWorkbook.Worksheets("Raw Data").AutoFilterMode = False
    ActiveWorkbook.Worksheets("Raw Data").Range("A1").Select
        
Next i

Since I'm using for loop, It's executing for ever for just 25K rows of data. Can I know better way to handle?

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
Praveen Kumar
  • 107
  • 1
  • 7
  • Do you actually have 300k rows of data? – Tim Williams Nov 25 '20 at 06:58
  • _Can I know better way to handle_. Sure: 1) [don't rely on Select/ActiveSheet](https://stackoverflow.com/q/10714251/445425) 2) don't hard code the ranges, determine the actual range similar to what you've already done with `For Each c In Range("M2:M" & Range("A" & Rows.Count).End(xlUp)` – chris neilsen Nov 25 '20 at 08:35

0 Answers0