Modify existing Macro with if and nullstring

[eclaudio]

Please help me understand and modify this macro.

My colleague wrote the macro listed below. It works just fine. Now I would like to add column P to do the same time. Can someone explain the If and null string part of the macro and help me modify it. I would like to learn this, so in the future I can write my very own.

Thank you in advance.

    Application.ScreenUpdating = False
    With ActiveSheet
    '' Sort Ascending Column O so all the items are grouped together
        .UsedRange.Sort Key1:=Range("o2"), Header:=xlYes
    '' Fill Columns S-Z with "_" if O is not blank
        For i = 2 To .UsedRange.rows.Count
            If .Cells(i, "o") <> vbNullString Then .Cells(i, "S").Resize(, 8) = "_"
        Next
    End With
    Application.ScreenUpdating = True

 

[Beyond_avarice]

  Application.ScreenUpdating = False
  Application.ScreenUpdating = True

Excel knows where your data is and doesn't need to visual see changes to perform tasks. This is a way of speeding up Macros by not having to consume resources to redraw your screen with each change.

    With ActiveSheet
   
    End With

This is a way to shorten having to continually qualify the worksheet with each new procedure.

    '' Sort Ascending Column O so all the items are grouped together
        .UsedRange.Sort Key1:=Range("o2"), Header:=xlYes

This is sorting your date via Column "O" and excluding the header from being sorted into your data.

        For i = 2 To .UsedRange.rows.Count
         
        Next

This is a loop. It is taking an iteration of "I" as starting with the value of 2 and incrementally increasing by one until each reaches the value of ".UsedRange.rows.Count". " .UsedRange.rows.Count" is identifying the range that is being consumed by your data and asking for a count of how many rows that is. The loop will incrementally increase until each reaches the final row number.

            If .Cells(i, "o") <> vbNullString Then .Cells(i, "S").Resize(, 8) = "_"

This is a logical statement that asks a question and the result of True or False will perform the respective procedure. In this case you have cells of row number(eg i=2) and column "O' or "O2". If the value of that cell is not null(no value), then the True condition will be triggered and Cell "S2" and seven more columns of cells to the right will made to equal "_".

            If .Cells(i, "o") <> vbNullString Then 
                        .Cells(i, "P") = "_"
                       .Cells(i, "S").Resize(, 8) = "_"

The above will do the same in Column "P".

 

[eclaudio]

  Application.ScreenUpdating = False
  Application.ScreenUpdating = True

Excel knows where your data is and doesn't need to visual see changes to perform tasks. This is a way of speeding up Macros by not having to consume resources to redraw your screen with each change.

    With ActiveSheet
   
    End With

This is a way to shorten having to continually qualify the worksheet with each new procedure.

    '' Sort Ascending Column O so all the items are grouped together
        .UsedRange.Sort Key1:=Range("o2"), Header:=xlYes

This is sorting your date via Column "O" and excluding the header from being sorted into your data.

        For i = 2 To .UsedRange.rows.Count
         
        Next

This is a loop. It is taking an iteration of "I" as starting with the value of 2 and incrementally increasing by one until each reaches the value of ".UsedRange.rows.Count". " .UsedRange.rows.Count" is identifying the range that is being consumed by your data and asking for a count of how many rows that is. The loop will incrementally increase until each reaches the final row number.

            If .Cells(i, "o") <> vbNullString Then .Cells(i, "S").Resize(, 8) = "_"

This is a logical statement that asks a question and the result of True or False will perform the respective procedure. In this case you have cells of row number(eg i=2) and column "O' or "O2". If the value of that cell is not null(no value), then the True condition will be triggered and Cell "S2" and seven more columns of cells to the right will made to equal "_".

            If .Cells(i, "o") <> vbNullString Then 
                        .Cells(i, "P") = "_"
                       .Cells(i, "S").Resize(, 8) = "_"

The above will do the same in Column "P".

Thank you, thank you for the explanation. It makes sense! I have a better idea how to use the true/false statement.