Find min and max dates to paste in Axis Options

[bastian18]

Hello.


I have a code to find min and max dates in Range("A") and Range("A2") and a MsgBox displaying min date(in format number) and another MsgBox displaying min date (in format number).
This is my code and works fine:

Sub minmaxdate()
Dim rng As Range
    Dim ws As Worksheet
    Dim ch As Chart
    Dim a As Range
    Dim b As Range 
    
    Set ws = Worksheets("qry_creategantt")
Set a = Range("A2:A" & Rows.Count)
        With a
            .Application.WorksheetFunction.Min (a)
        End With
        MsgBox Application.WorksheetFunction.Min(a)
        
     Set b = Range("b2:b" & Rows.Count)
        With b
            .Application.WorksheetFunction.Max (b)        
        End With
        MsgBox Application.WorksheetFunction.Max(b)
End Sub

However, instead of Msgboxes I need with my code doing this:


1. Find min date value, convert to number, go to Axis Option and paste value in Fixed Min
2. Find max date value, convert to number, go to Axis Option and paste value in Fixed Max

How can I modify my code to do that?

PD. ch is a variable I am using to create a Gantt chart.
Thanks

 

[bastian18]

Please, see that in Range("A") and Range("A2")
the correct line must be Range("B") and Range("B2").
Cheers

 

[bastian18]

Hello.
Finally d, I found the code lines to modified my code.
This is my code:

Sub [COLOR=#333333]minmaxdate()[/COLOR]
     
     Dim a, b As Range
          Set a = Range("A2:A" & Rows.Count)
               With a
                    .Application.WorksheetFunction.Min (a)
               End With
    
          Set b = Range("b2:b" & Rows.Count)
               With b
                   .Application.WorksheetFunction.Max (b) '
          End With
    
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    
    ActiveChart.Axes(xlValue).MinimumScale = ActiveChart.Application.WorksheetFunction.Min(a)
          
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select


End Sub

How can I avoid using in my code?

ActiveSheet ActiveChart Activate ("Chart X")

Also, I would appreciate any advice to write minimal lines in my code.

Cheers