snippets for excel and VBA

addsheets.bas

Sub AddWorksheet()

Worksheets.Add().Name = "MySheet"

End Sub



Sub AddAsLastWorksheet()

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"

End Sub



Sub AddXWorksheets()

Worksheets.Add After:=Worksheets(Worksheets.Count), Count:=4

End Sub

create_csv_file.bas

Sub CreateCSV()
' Create CSV file from sheet

    Dim rCell As Range
    Dim rRow As Range
    Dim sOutput As String
    Dim sFname As String, lFnum As Long
        
    'Open a text file to write
    sFname = "C:\MyCsv.csv"
    lFnum = FreeFile
    
    Open sFname For Output As lFnum
    'Loop through the rows'
        For Each rRow In ActiveSheet.UsedRange.Rows
        'Loop through the cells in the rows'
        For Each rCell In rRow.Cells
            sOutput = sOutput & rCell.Value & ";"
        Next rCell
         'remove the last comma'
        sOutput = Left(sOutput, Len(sOutput) - 1)
        
        'write to the file and reinitialize the variables'
        Print #lFnum, sOutput
        sOutput = ""
     Next rRow
    
    'Close the file'
    Close lFnum
    
End Sub

usedrows.bas

Sub usedrows()

'To count used range of cells
MsgBox ActiveSheet.UsedRange.Rows.Count

End Sub