Hi all,

My problem is that this simple task that is run maybe 30 or 40 times (micro seconds for a computer) is taking 4 seconds to run, when i block the "with" out below, the sub (which contains this plus additional code) takes less than 1 second to run. I can live with one second, but not 4! items will be entered fast and 4 second lag is not something we can live with.


variables:
nrow = Worksheets("HGL").Columns(1).Find(what:=Header(iii)).Row (first row of range being named below)
llr = last row of the range that starts above on the header's row
Header(iii) - this is the array that is pulled from during the loop to name the range (changes for each iii)



The loop runs through each header (array), finds the starting and ending row matching that array text and then names is accordingly.


This is the portion i am having issues with:
 With Range("F" & nrow, "F" & llr)        
         .Name = Header(iii)
    End With
And here is the whole loop:


 'the code below sorts through column C and finds the first blank cell (line between pipe runs)
'and gives the rows bewtween said last row and the first row
'(row with header in col A) a range name of whatever the header string is (SD.A12, or something of the like)


For iii = 1 To rayl                           'rayl is the length of the array titled "Header"
    
'the below finds the header we are searching for (header(iii)) and returns its row num
    nrow = Worksheets("HGL").Columns(1).Find(what:=Header(iii)).Row


    
'starting on the row that Header(iii) is found on, this runs through the rows until the first blank row is found
    For i = nrow To lr + 1
        If Range("C" & i).Value = vbNullString Then
            llr = i - 1 'llr is local last row, meaning the last row of the range that will be going in the above header
            Exit For
        End If
    Next i
    


'this is the portion that names the ranges, _Q is the name for the flow range being taken in by the pipe
    With Range("F" & nrow, "F" & llr)
        .Name = Header(iii)
    End With


     With Range("Q" & nrow, "Q" & llr)
        .Name = Header(iii) & "_Q"
    End With
    
    
Next iii
To answer the question about why I don't just set up a button to run this macro after everything is inputted: I don't trust that people will manually update the named ranges. Therefore, this code will run every time an item within a specific range changes.


Thanks for any input!