Minimize and Optimize VBA Code !!

[Pramodpandit123]

As I am just starter in VBA the code that I've made(with help of this forum via @Yongle ) is too bulky and runs too slow. I need to reduce the code size and make it run faster !!

Option Explicit

Private Sub RUN()
    Dim ws As Worksheet, ocol As Long, x As Long, y As Long, orow As Long, m As Long, n As Long, o As Long
    Dim cel As Range, cel4 As Range, cel5 As Range, cel6 As Range, cel2 As Range, cel0 As Range, cel3 As Range, cel7 As Range, a As Range
    Dim shp1 As Shape, shp2 As Shape, shp3 As Shape, shp4 As Shape, shp As Shape
    Set ws = ActiveSheet
    orow = 4
    ocol = 4
    x = ws.Range("A4").Value
    y = ws.Range("A5").Value
'number shapes
    Set cel = Range("E6")
    Set cel0 = cel.Offset(orow * y, 0)
    Set cel2 = cel.Offset(1, 0)
    Set cel3 = cel0.Offset(-1, 0)
    Set cel4 = cel.Offset(0, 2)
    Set cel5 = cel.Offset(3, 0)
    Set cel6 = cel.Offset(5, 0)
    Set cel7 = cel.Offset(2, 0)
    For m = 1 To x
        Set shp = ws.Shapes.AddShape(msoShapeOval, cel.Left, cel.Top, cel.Width, cel.Width)
        Set shp1 = ws.Shapes.AddShape(msoShapeOval, cel0.Left, cel0.Top, cel0.Width, cel0.Width)
       Set shp2 = ws.Shapes.AddLine(cel2.Left + cel2.Width / 2, cel2.Top, cel2.Left + cel2.Width / 2, cel2.Top + cel2.Height)
       Set shp3 = ws.Shapes.AddLine(cel3.Left + cel3.Width / 2, cel3.Top, cel3.Left + cel3.Width / 2, cel3.Top + cel3.Height)
        shp.Select
         With Selection.ShapeRange
            .Fill.Visible = msoFalse
            With .TextFrame
             .Characters.Text = m
                .Characters.Font.ColorIndex = 3
                .HorizontalAlignment = xlHAlignCenter
                .VerticalAlignment = xlVAlignCenter
            End With
            With .Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
            End With
        End With
        shp1.Select
        With Selection.ShapeRange
            .Fill.Visible = msoFalse
            With .TextFrame
             .Characters.Text = m
                .Characters.Font.ColorIndex = 3
                .HorizontalAlignment = xlHAlignCenter
                .VerticalAlignment = xlVAlignCenter
            End With
            With .Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
            End With
             shp2.Select
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        .DashStyle = msoLineLongDashDotDot
        .Weight = 1.5
            End With
            shp3.Select
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        .DashStyle = msoLineLongDashDotDot
        .Weight = 1.5
            End With
             For n = 1 To y - 1
       Set shp4 = ws.Shapes.AddLine(cel5.Left + cel5.Width / 2, cel5.Top, cel6.Left + cel6.Width / 2, cel6.Top + cel6.Height)
            shp4.Select
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        .DashStyle = msoLineLongDashDotDot
        .Weight = 1.5
            End With
             Set cel5 = cel5.Offset(orow, 0)
        Set cel6 = cel6.Offset(orow, 0)
            Next
            cel4.Select
            With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Interior.Pattern = xlSolid
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorAccent6
        .Interior.TintAndShade = 0.4
        .Interior.PatternTintAndShade = 0
        End With
        For o = 1 To y
        cel7.Value = "C1"
         cel7.Select
            With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        End With
        Set cel7 = cel7.Offset(orow, 0)
        Next
        End With
        Set cel5 = cel.Offset(3, 0)
        Set cel6 = cel.Offset(5, 0)
        Set cel7 = cel.Offset(2, 0)
        Set cel = cel.Offset(0, ocol)
        Set cel0 = cel0.Offset(0, ocol)
        Set cel2 = cel2.Offset(0, ocol)
        Set cel3 = cel3.Offset(0, ocol)
        Set cel5 = cel5.Offset(0, ocol)
        Set cel6 = cel6.Offset(0, ocol)
        Set cel7 = cel7.Offset(0, ocol)
        Set a = cel.Offset(0, ocol)
        If m < x - 1 Then
        Set cel4 = cel.Offset(0, ocol - 2)
        End If
        Next

'alpha shapes
    Set cel = Range("C8")
    Set cel0 = cel.Offset(0, ocol * x)
    Set cel2 = cel.Offset(0, 1)
    Set cel3 = cel0.Offset(0, -1)
      Set cel4 = cel.Offset(2, 0)
        Set cel5 = cel.Offset(0, 3)
    Set cel6 = cel.Offset(0, 5)
            For m = 1 To y
        Set shp = ws.Shapes.AddShape(msoShapeOval, cel.Left, cel.Top, cel.Width, cel.Width)
        Set shp1 = ws.Shapes.AddShape(msoShapeOval, cel0.Left, cel0.Top, cel0.Width, cel0.Width)
        Set shp2 = ws.Shapes.AddLine(cel2.Left, cel2.Top + cel2.Height / 2, cel2.Left + cel2.Width, cel2.Top + cel2.Height / 2)
          Set shp3 = ws.Shapes.AddLine(cel3.Left, cel3.Top + cel3.Height / 2, cel3.Left + cel3.Width, cel3.Top + cel3.Height / 2)
        shp.Select
        With Selection.ShapeRange
            .Fill.Visible = msoFalse
            With .TextFrame
             .Characters.Text = Chr(m + 64)
                .Characters.Font.ColorIndex = 3
                .HorizontalAlignment = xlHAlignCenter
                .VerticalAlignment = xlVAlignCenter
            End With
            With .Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
            End With
        End With
        shp1.Select
        With Selection.ShapeRange
            .Fill.Visible = msoFalse
            With .TextFrame
             .Characters.Text = Chr(m + 64)
                .Characters.Font.ColorIndex = 3
                .HorizontalAlignment = xlHAlignCenter
                .VerticalAlignment = xlVAlignCenter
            End With
            With .Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0
            End With
            shp2.Select
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        .DashStyle = msoLineLongDashDotDot
        .Weight = 1.5
            End With
            shp3.Select
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        .DashStyle = msoLineLongDashDotDot
        .Weight = 1.5
        End With
        For n = 1 To x - 1
       Set shp4 = ws.Shapes.AddLine(cel5.Left, cel5.Top + cel5.Height / 2, cel6.Left + cel6.Width, cel6.Top + cel6.Height / 2)
            shp4.Select
        With Selection.ShapeRange.Line
            .Visible = msoTrue
            .ForeColor.RGB = RGB(255, 0, 0)
        .DashStyle = msoLineLongDashDotDot
        .Weight = 1.5
            End With
             Set cel5 = cel5.Offset(0, ocol)
        Set cel6 = cel6.Offset(0, ocol)
            Next
         cel4.Select
            With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Interior.Pattern = xlSolid
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorAccent6
        .Interior.TintAndShade = 0.4
        .Interior.PatternTintAndShade = 0
        End With
        End With
        Set cel5 = cel.Offset(0, 3)
        Set cel6 = cel.Offset(0, 5)
        Set cel = cel.Offset(orow, 0)
        Set cel0 = cel0.Offset(orow, 0)
        Set cel2 = cel2.Offset(orow, 0)
        Set cel3 = cel3.Offset(orow, 0)
        Set cel5 = cel5.Offset(orow, 0)
        Set cel6 = cel6.Offset(orow, 0)
        If m < y - 1 Then
        Set cel4 = cel.Offset(orow - 2, 0)
        End If
        Next

ws.Range("B4").Select

End Sub

 

[Yongle]

that looks rather different to the code I last saw

 

[Yongle]

if you want help it would be a good idea to show the expected output from the code and explain how you plan to use it etc
- there are lots of issues in your code and I think it would be simplest to start afresh

 

[Pramodpandit123]

I modified the according to my need by using your code as Foundation/Base.Its main purpose is to use it as visual representation to show Gridlines ie as column seperator.
What are the issues do you see in code ? Would you be kind enough to just point my mistakes/improvements to be made on it briefly so that i could further will dig on that subject and learn !!