Optimize my code

[jackeroo75]

I know I can do this better but I'm not sure how to optimize my code.

Const sWorkRel1 As String = "1-*"
Const sWorkRel2 As String = "3-*"
Const sWorkRel3 As String = "4-*"
Const sWorkRel4 As String = "49*"
Const sFloora As String = "11*"
Const sFloorb As String = "10*"
Const sFloorc As String = "9*"
Const sFloord As String = "8*"
Const sFloore As String = "7*"
Const sFloorf As String = "4*"
Const sFloorg As String = "3*"
Const sFloorh As String = "2*"
 
Dim rngMyRange As Range, rngCell As Range
Dim pagebreakcount As Integer, floor1 As Integer, floor2 As Integer, floor3 As Integer
Dim floor4 As Integer, floor7 As Integer, floor8 As Integer, floor9, floorall As Integer
    
Dim flag1 As Integer, flag2 As Integer, flag3 As Integer, flag4 As Integer
Dim flag7 As Integer, flag8 As Integer, flag9 As Integer, flagall As Integer, flagrjc As Integer
    
    
pagebreakcount = g_startRow + 1
With Worksheets("List")
  Set rngMyRange = .Range(.Cells(g_startRow + 1, 1), .Cells(endofRow - 1, 1))
  'the range to work with
  For Each rngCell In rngMyRange
  'loop through the range
    If (rngCell.Value Like sWorkRel1 Or rngCell.Value Like sWorkRel2 Or rngCell.Value Like sWorkRel3 Or rngCell.Value Like sWorkRel4) Then
      pagebreakcount = pagebreakcount + 1
      floorall = pagebreakcount
      flagall = 1
    
    ElseIf (rngCell.Value Like sFloora Or rngCell.Value Like sFloorb) Then
      pagebreakcount = pagebreakcount + 1
      floor1 = pagebreakcount
      flag1 = 1
    ElseIf (rngCell.Value Like sFloorh Or rngCell.Value Like sFloorg) Then
      pagebreakcount = pagebreakcount + 1
      floor2 = pagebreakcount
      flag2 = 1
    ElseIf rngCell.Value Like sFloorf Then
      pagebreakcount = pagebreakcount + 1
      floor4 = pagebreakcount
      flag4 = 1
    ElseIf rngCell.Value Like sFloore Then
      pagebreakcount = pagebreakcount + 1
      floor7 = pagebreakcount
      flag7 = 1
    ElseIf rngCell.Value Like sFloord Then
      pagebreakcount = pagebreakcount + 1
      floor8 = pagebreakcount
      flag8 = 1
    ElseIf rngCell.Value Like sFloorc Then
      pagebreakcount = pagebreakcount + 1
      floor9 = pagebreakcount
      flag9 = 1
    Else
      pagebreakcount = pagebreakcount + 1
      floorall = pagebreakcount
      flagall = 1
    End If
  Next
  If flag1 Then
    .HPageBreaks.Add Before:=Cells(floor1, 1)
  End If
  If flag2 Then
    .HPageBreaks.Add Before:=Cells(floor2, 1)
  End If
  If flag4 Then
    .HPageBreaks.Add Before:=Cells(floor4, 1)
  End If
  If flag7 Then
    .HPageBreaks.Add Before:=Cells(floor7, 1)
  End If
  If flag8 Then
    .HPageBreaks.Add Before:=Cells(floor8, 1)
  End If
  If flag9 Then
    .HPageBreaks.Add Before:=Cells(floor9, 1)
  End If
  If flagall Then
    .HPageBreaks.Add Before:=Cells(floorall, 1)
  End If
End With

 

[SkipVought]

I'd try using arrays and loops. It would reduce the LOC and maybe simplify the logic.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jackeroo75]

Use arrays and loops instead of the if else statement?

 

[jackeroo75]

Use arrays and loops instead of the if else statement? Also, what is LOC?

 

[SkipVought]

Lines Of Code

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]