Need help cleaning up my code...

[pasqualebaldi]

I have a repetitive code that skips every 20 lines and starts on C21 and goes goes to C2921. I'm sure there is a way to shorten this code, I just do not know how.

I appreciate the help.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Row Mod 22 = 0 Then
        If Target.Value <> "" Then
            Select Case Target.Value
                Case "Blank Sheet": Msgbox_Blank_Sheet
                Case "Place Concrete": Msgbox_Concrete
                Case "Asphalt": Msgbox_Asphalt
                Case "Silicone Joints": Msgbox_Silicone_Joints
                Case "Compression Seal": Msgbox_Compression_Seal
                Case "CTB": Msgbox_CTB
                Case "CTS": Msgbox_CTS
                Case "Aggregate Base": Msgbox_Aggregate_Base

            End Select
        End If
    End If
End If
    
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Row Mod 42 = 0 Then
        If Target.Value <> "" Then
            Select Case Target.Value
                Case "Blank Sheet": Msgbox_Blank_Sheet
                Case "Place Concrete": Msgbox_Concrete
                Case "Asphalt": Msgbox_Asphalt
                Case "Silicone Joints": Msgbox_Silicone_Joints
                Case "Compression Seal": Msgbox_Compression_Seal
                Case "CTB": Msgbox_CTB
                Case "CTS": Msgbox_CTS
                Case "Aggregate Base": Msgbox_Aggregate_Base

            End Select
        End If
    End If
End If

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target.Row Mod 62 = 0 Then
        If Target.Value <> "" Then
            Select Case Target.Value
                Case "Blank Sheet": Msgbox_Blank_Sheet
                Case "Place Concrete": Msgbox_Concrete
                Case "Asphalt": Msgbox_Asphalt
                Case "Silicone Joints": Msgbox_Silicone_Joints
                Case "Compression Seal": Msgbox_Compression_Seal
                Case "CTB": Msgbox_CTB
                Case "CTS": Msgbox_CTS
                Case "Aggregate Base": Msgbox_Aggregate_Base

and so on..............
and so on..............

[mikeTRON]

Are you asking how to loop through the code from 21 to 2921?

DIM I as integer

For i = 21 to 2291 step 20
     do work
next i

[HaHoBe]

Hi, pasqualebaldi,

maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A21:A2921")) Is Nothing Then
  If Target.Row Mod 2 = 0 And Target.Row Mod 20 = 2 Then
    If Target.Value <> "" Then
      Select Case Target.Value
          Case "Blank Sheet": Msgbox_Blank_Sheet
          Case "Place Concrete": Msgbox_Concrete
          Case "Asphalt": Msgbox_Asphalt
          Case "Silicone Joints": Msgbox_Silicone_Joints
          Case "Compression Seal": Msgbox_Compression_Seal
          Case "CTB": Msgbox_CTB
          Case "CTS": Msgbox_CTS
          Case "Aggregate Base": Msgbox_Aggregate_Base
          Case Else
      End Select
    End If
  End If
End If
End Sub

BTW: the code works only on Column A, where is the relation to Column C as you give up that range?

Ciao,
Holger

[pasqualebaldi]

Hi, pasqualebaldi,

maybe like this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A21:A2921")) Is Nothing Then
  If Target.Row Mod 2 = 0 And Target.Row Mod 20 = 2 Then
    If Target.Value <> "" Then
      Select Case Target.Value
          Case "Blank Sheet": Msgbox_Blank_Sheet
          Case "Place Concrete": Msgbox_Concrete
          Case "Asphalt": Msgbox_Asphalt
          Case "Silicone Joints": Msgbox_Silicone_Joints
          Case "Compression Seal": Msgbox_Compression_Seal
          Case "CTB": Msgbox_CTB
          Case "CTS": Msgbox_CTS
          Case "Aggregate Base": Msgbox_Aggregate_Base
          Case Else
      End Select
    End If
  End If
End If
End Sub

BTW: the code works only on Column A, where is the relation to Column C as you give up that range?

Ciao,
Holger

I'm sorry, when one of the cases are picked it pastes data in column C. I meant to say column A.

Thank you very much! This worked perfectly and it is much less code than what I had!