VBS VBA

[7dir]

Dim shll : Set shll = CreateObject("WScript.Shell")
Set Rt = shll.Exec("Notepad") : wscript.sleep 4000 : Rt.Terminate

[7dir]

read csv

Dim FilePath As String

    FilePath = "e:\TradeHouse_Excel\КнигаПродаж\authors.csv"
    Open FilePath For Input As #1
        row_number = 0
        Do Until EOF (1)
            Line Input #1, LineFromFile
            LineItems = Split(LineFromFile, ",")

            ActiveCell.Offset (row_number, 0).Value = LineItems (2)
            ActiveCell.Offset (row_number, 1).Value = LineItems (1)
            ActiveCell.Offset (row_number, 2).Value = LineItems (0)

            row_number = row_number + 1
        Loop
    Close #1

[7dir]

add row before last

Sub add_row()
'
'
    Dim sht As Worksheet
    Dim LastRow As Long

    Set sht = ActiveSheet

    LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    LastColumn = sht.Range("A" & LastRow).CurrentRegion.Columns.Count

    cell_last_row_num = sht.Cells(LastRow - 1, 1).Value
    'MsgBox cell_last_row_num

    Rows(LastRow - 1 & ":" & LastRow - 1).Select
    Selection.Copy
    Rows(LastRow & ":" & LastRow).Select
    Selection.Insert Shift:=xlDown
    sht.Cells(LastRow, 1).Value = cell_last_row_num + 1

'
End Sub

[7dir]

Ways To Find The Last Column

Sub FindingLastColumn()

'PURPOSE: Different ways to find the last column number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastColumn As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Ctrl + Shift + End
  LastColumn = sht.Cells(7, sht.Columns.Count).End(xlToLeft).Column

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastColumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

'Using Table Range
  LastColumn = sht.ListObjects("Table1").Range.Columns.Count

'Using Named Range
  LastColumn = sht.Range("MyNamedRange").Columns.Count

'Ctrl + Shift + Right (Range should be first cell in data set)
  LastColumn = sht.Range("A1").CurrentRegion.Columns.Count

End Sub

[7dir]

Ways To Find The Last Row

Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
'SOURCE: www.TheSpreadsheetGuru.com

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

'Using Find Function (Provided by Bob Ulmas)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Using SpecialCells Function
  LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row

'Ctrl + Shift + End
  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

'Using UsedRange
  sht.UsedRange 'Refresh UsedRange
  LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

'Using Table Range
  LastRow = sht.ListObjects("Table1").Range.Rows.Count

'Using Named Range
  LastRow = sht.Range("MyNamedRange").Rows.Count

'Ctrl + Shift + Down (Range should be first cell in data set)
  LastRow = sht.Range("A1").CurrentRegion.Rows.Count

End Sub