Selecting range example in VBA

Sub SelectingLastCellOfContiguousRange()

'Go To last cell

ActiveSheet.Range("a1").End(xlDown).Select

End Sub

Sub SelectingBlankCellOfContiguousRange()

'Go to first blank cell after laste cell

ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select

End Sub

Sub SelectEntireRangeofContiguousCells()

'Select Range Of Cells No Blanks

ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select

ActiveSheet.Range("a1:" & ActiveSheet.Range("a1").End(xlDown).Address).Select

End Sub

Sub SelectEntireRangeofNonContiguousCells()

'Select Range of Cells That Includes Blanks

ActiveSheet.Range("a1", ActiveSheet.Range("a65536").End(xlUp)).Select

ActiveSheet.Range("a1:" & ActiveSheet.Range("a65536").End(xlUp).Address).Select

End Sub

Sub SelectEntire()

'Select Entire Row

Range("1:1").Select

'Select Entire Column

Range("A:A").Select

End Sub

Sub SelectRectangularRange()

'Select Current Region

'ActiveSheet.Range("a1").CurrentRegion.Select

'ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select

'ActiveSheet.Range("a1:" & ActiveSheet.Range("a1").End(xlDown).End(xlToRight).Address).Select

'Build Current Region

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column

lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select

'Including a blank row

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column

lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

ActiveSheet.Range("a1:" & ActiveSheet.Cells(lastRow, lastCol).Address).Select

End Sub

Sub SelectMultiNonContColumns()

StartRange = "A1"

EndRange = "C1"

Set a = Range(StartRange, Range(StartRange).End(xlDown))

Set b = Range(EndRange, Range(EndRange).End(xlDown))

Union(a, b).Select

End Sub

Attribute VB_Name = "SelectingLast"

VBA Macro to run linear regression between two variables,VBA Teacher Sourav,Kolkata 08910141720

I have observations for two variable ,the column speed is for X variable,The column distance is for Y variable

Now I have Data Analysis toolpack installed,so this macro will calculate linear regression for X and Y

Sub automatelinearregression()
Sheets("Sheet1").Select

lrA = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (lrA)

lrC = Cells(Rows.Count, "B").End(xlUp).Row
MsgBox (lrC)
lr = Application.Max(lrA, lrC)  'or Min? I expect the x and y have to have the same number of values?
MsgBox (lr)
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("B1:B" & lr), ActiveSheet.Range("A1:A" & lr), False, True, 95, ActiveSheet.Range("$O$5"), False, False, False, False, , False
End Sub

Result:

Source:http://www.vbaexpress.com/forum/showthread.php?55218-VBA-to-run-a-Linear-Regression-Automatically

Using Spin Button to increase or decrease date in VBA

Private Sub SpinButton1_SpinDown()

Dim datevar As Date
If Me.TextBox1.Text <> "" Then
On Error Resume Next
 datevar = DateValue(Me.TextBox1.Text)
 Else
 datevar = Date
 End If
 Me.TextBox1.Text = (datevar - 1)

End Sub

Private Sub SpinButton1_SpinUp()
If Me.TextBox1.Text <> "" Then
On Error Resume Next
 datevar = DateValue(Me.TextBox1.Text)
 Else
 datevar = Date
 End If
 Me.TextBox1.Text = (datevar + 1)
End Sub

Automating Print preview of excel reports using vba

'this is for setting the center header of print which will print as Active Employee List
ersheet.PageSetup.CenterHeader = "Active Employee List"
'this is for setting the right footer of print which will print as Page (number of current page) of Total Pages
ersheet.PageSetup.RightFooter = "Page &P of &N"

'this commented out section works best with portrait printing
'ersheet.PageSetup.Zoom = 60
'ersheet.PageSetup.FitToPagesWide = 1

'ersheet.PageSetup.FitToPagesTall = False

'this section works best for landscape printing
With ersheet.PageSetup
'for setting portrait or landscape
.Orientation = xlLandscape
'these next two line will fit all columns in one page
.FitToPagesWide = 1
.FitToPagesTall = 1
'this line is responsible for continuing one fixed row on several print pages ,it is similar as excel freeze pane
.PrintTitleRows = ersheet.Rows(1).Address
End With

ersheet.PrintPreview

Output

Source:

https://docs.softartisans.com/officewriterwindows/3.0.5/ExcelWriterASP/features/headersandfooters.aspx

https://stackoverflow.com/questions/34052790/vba-code-to-set-print-area-fit-to-1x1-page-and-not-set-print-area-for-certain-t

https://www.youtube.com/watch?v=X4QBS94iNdo&list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U&index=13

Creating Moving Average chart automatically based on 4 series collection(Moving Averages,UCL,CL,LCL) using Excel VBA ,VBA Teacher Sourav,Kolkata 08910141720

Sub movingrangechartmacro()

Dim co As ChartObject
    Dim ct As Chart
    Dim scl As SeriesCollection
    Dim ser1 As Series
    Dim chartname As String

    Sheets("MovingRange").Select

       Set co = Worksheets("MovingRange").ChartObjects.Add(Range("H8").Left, Range("H8").Top, 2500, 500)
       
       'chartname = Worksheets("process_capability").Range("D15").Value
       chartname = "movingrange"
       co.Name = chartname
      ' MsgBox (co.Name)
       Set ct = co.Chart
       With ct
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Text = "Moving Range Chart"
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("B1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("B1").Offset(1, 0), Worksheets("MovingRange").Range("B1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("D1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("D1").Offset(1, 0), Worksheets("MovingRange").Range("D1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("E1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("E1").Offset(1, 0), Worksheets("MovingRange").Range("E1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
       
       
       
       Set sc1 = .SeriesCollection
       Set ser1 = sc1.NewSeries
       With ser1
       .Name = Worksheets("MovingRange").Range("F1").Value
       .xvalues = Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown))
       .Values = Range(Worksheets("MovingRange").Range("F1").Offset(1, 0), Worksheets("MovingRange").Range("F1").End(xlDown))
       .ChartType = xlXYScatterLinesNoMarkers
       '.Select
       '.Smooth = True
        
       End With
      
       .Axes(xlValue).MajorGridlines.Select
         Selection.Delete
         Dim high, low As Long
         
      high = Round(((Application.WorksheetFunction.max(.SeriesCollection(2).Values))), 0)
      'MsgBox (high)
         low = Round(((Application.WorksheetFunction.min(.SeriesCollection(4).Values))), 0)
      'MsgBox (low)
      .Axes(xlValue).Select
      .Axes(xlValue).MinimumScale = low
      .Axes(xlValue).MaximumScale = high
      .Axes(xlValue).MajorUnit = 0.5
      Range(Worksheets("MovingRange").Range("A1").Offset(1, 0), Worksheets("MovingRange").Range("A1").End(xlDown)).Select
    high = Round(Application.WorksheetFunction.max(Selection), 0)
    'MsgBox (high)
     low = Round(Application.WorksheetFunction.min(Selection), 0)
     .Axes(xlCategory).Select
      .Axes(xlCategory).MinimumScale = low
      .Axes(xlCategory).MaximumScale = high
      .Axes(xlCategory).MajorUnit = 1
     
     
     'X axis name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Day Index"
'y-axis name
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Gas Use"

'coloring and designing the marker and the data series

Set sc1 = ct.SeriesCollection(1)
With sc1

    .Format.Line.Weight = 2 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(0, 0, 0) 'now it works

 .MarkerSize = 8
 .MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 .MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With
    
    
     
       
 'coloring UCL seriescollection
 Set sc1 = ct.SeriesCollection(2)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With

  'coloring LCL seriescollection
 Set sc1 = ct.SeriesCollection(4)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With
     
     
  'coloring CL seriescollection
 Set sc1 = ct.SeriesCollection(3)
With sc1

    .Format.Line.Weight = 4 'Line.Weigth works ever

 .Format.Line.Visible = msoFalse 'for Line.ForeColor getting to work we have to cheat something
 .Format.Line.Visible = msoTrue
 .Format.Line.ForeColor.RGB = RGB(0, 255, 0) 'now it works

 '.MarkerSize = 8
 '.MarkerBackgroundColor = RGB(0, 0, 0) 'marker background

 '.MarkerForegroundColor = RGB(0, 0, 0) 'marker foreground (lines around)
    
     End With


       
  End With
End Sub


The picture of chart to be created

The source Data

The video where the chart is created manually

https://www.youtube.com/watch?v=uGgd3iiQrUI

The link from stackoverflow which helped me regarding marker on series collection on a chart issue 

https://stackoverflow.com/questions/29675485/excel-vba-line-color-marker-line-color?answertab=votes#tab-top