Repeat until a row is empty in VB

[gmazza76]

Good morning,

I have created the code below, but it is slowing the file loading process up as I have entered a range to search instead of the Column, due to data being in some rows above Row 11
Is there anyway to get the code to work its way down the column C & AM to the last full row instead of going down to row 50,000

Sorry I havent used VB in a while can I combine the first section in column C as 1 piece of coding and then the VB for Column AM in 1 piece as well instead of a line for each description in the speech marks?

For Each Cel In Range("C11:C50000")
        If Cel.Value <> "" Then Cel.Offset(0, 42).Value = "10"
Next
For Each Cel In Range("C11:C50000")
        If Cel.Value <> "" Then Cel.Offset(0, 43).Value = "1"
Next
For Each Cel1 In Range("AM11:AM50000")
        If Cel1.Value = "Passive" Then Cel1.Offset(0, 8).Value = "1"
Next    
'Criteria change
For Each Cel1 In Range("AM11:AM50000")
        If Cel1.Value = "Promoter" Then Cel1.Offset(0, 9).Value = "1"
Next
For Each Cel1 In Range("AM11:AM50000")
        If Cel1.Value = "Detractor" Then Cel1.Offset(0, 10).Value = "1"
 Next
 For Each Cel1 In Range("AM11:AM50000")
        If Cel1.Value = "Strong Detractor" Then Cel1.Offset(0, 11).Value = "1"
 Next

thanks in advance

 

[Alex Blakenburg]

Do we need to test both columns for the last row or will they always both end in the same row ?
Are there any formulas in the output columns ? If not it will be faster if we use an array but that would overwrite any formulas if they exist.

 

[gmazza76]

Good afternoon

No formulas in any of the outputs as long as 10 is put in column AS or Offset(0, 42) and 1 is put in AT or Offset(0, 43) that should be fine as long as Column C from row 12 isn't blank
The same with the last 4 outputs from the column AM Variable

thanks

 

[offthelip]

this should make it a bit faster but there is a much better way fo doing this with variant array:

For Each Cel In Range("C11:C50000")
        If Cel.Value <> "" Then
        Cel.Offset(0, 42).Value = "10"
        Cel.Offset(0, 43).Value = "1"
        End If
Next
For Each Cel1 In Range("AM11:AM50000")
        If Cel1.Value = "Passive" Then Cel1.Offset(0, 8).Value = "1"
        If Cel1.Value = "Promoter" Then Cel1.Offset(0, 9).Value = "1"
        If Cel1.Value = "Detractor" Then Cel1.Offset(0, 10).Value = "1"
        If Cel1.Value = "Strong Detractor" Then Cel1.Offset(0, 11).Value = "1"
Next

 

[Alex Blakenburg]

I have knocked it up prior to your response, so have not yet converted it to use an array and I have checked the last row on the 2 columns independantly.
Let me know how you go. If it is too slow I will modify it to use an array.

Sub gmazza76()

    Dim Cel As Range
    Dim lastRow As Long
    
    Application.ScreenUpdating = False
    
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    
    For Each Cel In Range("C11:C" & lastRow)
            If Cel.Value <> "" Then
                Cel.Offset(0, 42).Value = "10"
                Cel.Offset(0, 43).Value = "1"
            End If
    Next
    
    lastRow = Range("AM" & Rows.Count).End(xlUp).Row
    
    For Each Cel In Range("AM11:AM" & lastRow)
        Select Case Cel.Value
        
            Case "Passive":          Cel.Offset(0, 8).Value = "1"
            Case "Promoter":         Cel.Offset(0, 9).Value = "1"
            Case "Detractor":        Cel.Offset(0, 10).Value = "1"
            Case "Strong Detractor": Cel.Offset(0, 11).Value = "1"
            Case Else
            
        End Select

    Next

    Application.ScreenUpdating = True
    
End Sub

 

[gmazza76]

thanks for this is there anyway of changing it from a range to say the column from C11 downwards as sometimes there are only a few rows dependant upon who loads the file, this would mean I dont need to go in a few months and change the range when more data is uplaoded to the source file

 

[gmazza76]

To be honest it is on the slow side but it does what it needs to do which is a great help. How much faster would an array make it

 

[offthelip]

try this where I have used variant arrays to avoid lots access tothe worksheet ( but not all!!)

Sub gmazza76()

    Dim Cel As Range
    Dim lastRow As Long
    
    Application.ScreenUpdating = False
    
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    inarr = Range(Cells(1, 3), Cells(lastRow, 3))
'    For Each Cel In Range("C11:C" & lastRow)
    For i = 11 To lastRow
            If inarr(i, 1) <> "" Then
            'Range(Cells(i, 3+42), Cells(i, 3+42))
                Range(Cells(i, 3 + 42), Cells(i, 3 + 42)).Value = "10"
                Range(Cells(i, 3 + 43), Cells(i, 3 + 43)).Value = "1"
            End If
    Next i
    
    lastRow = Range("AM" & Rows.Count).End(xlUp).Row
    inarr = Range(Cells(1, 39), Cells(lastRow, 39))
    
'    For Each Cel In Range("AM11:AM" & lastRow)
     For i = 11 To lastRow
        Select Case inarr(i, 1)
        
            Case "Passive":          Range(Cells(i, 8 + 42), Cells(i, 8 + 42)).Value = "1"
            Case "Promoter":         Range(Cells(i, 9 + 42), Cells(i, 9 + 42)).Value = "1"
            Case "Detractor":        Range(Cells(i, 10 + 42), Cells(i, 10 + 42)).Value = "1"
            Case "Strong Detractor": Range(Cells(i, 11 + 42), Cells(i, 11 + 42)).Value = "1"
            Case Else
            
        End Select

    Next

    Application.ScreenUpdating = True
    
End Sub