open workbook

Workbooks.Open filename:="file.xls"
 Sheets(Worksheets.Count).Activate   ' selecting the last tab
'insert row after first line
  Rows("2").Select
 Selection.Insert Shift:=xlDown
ActiveSheet.Cells(2, 1).Value = "Sharieff"
ActiveSheet.Columns("A").AutoFit
  'If row 3 is blank then deleting it
        If LenB(ActiveSheet.Cells(3, 1)) = 0 Then
          ActiveSheet.Rows(3).EntireRow.Delete
          Rows("2").Select
        End If
  ActiveWorkbook.Close SaveChanges:=True

'Reading Textbox value
filename = Application.Worksheets("main").txtFile.Value

'Current Path
Path = Application.ThisWorkbook.Path

'Wild card
file = Path & "*juk*.xls"
file_return = Dir(file)
While file_return <> ""
        file_return = Dir()
wend

Add New workbook

Dim new_xl As Object

Dim workbook_name As String

Set new_xl = Workbooks.Add

new_xl.Activate

workbook_name = ActiveWorkbook.Name

Sheets("sheet1").Select

Cells(1, 1) = "CURRENT STOCK REPORT OF ALL PRODUCTS"

‘copy data from xl file x.xls

For i = 1 To lastrow

  Cells(i + 2, 1) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 1)

  Cells(i + 2, 2) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 2)

  Cells(i + 2, 3) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 3)

Next i

‘Formatting

Range("A1:F1").Select

    Selection.Merge

    With Selection

        .HorizontalAlignment = xlLeft

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = True

    End With

    With Selection.Interior

        .ColorIndex = 10

        .Pattern = xlSolid

    End With

    Selection.Font.ColorIndex = 2

    Selection.Font.Bold = True

    Cells.Select

    Cells.EntireColumn.AutoFit

End Sub

Find First or Last Populated Row in a sheet

Option Explicit

Sub Test_xlFirstLastRows()

    

     '       Target Application:  MS Excel

     '       Demonstration:  display first and last non-blank rows in the active sheet

     '                       and one target sheet

    

    Dim SheetName As String

     '

     '           display sheet name and results from xlFirstRow and xlLastRow

     '           for the active sheet.  Since activesheet is assumed if procs are called

     '           without a passed arguement, use that method here

     '

    MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _

    "First non-blank row = " & xlFirstRow & vbCrLf & _

    "Last non-blank row = " & xlLastRow, vbInformation, _

    "Active Sheet Demonstration"

     '

     '           display sheet name and results from xlFirstRow and xlLastRow

     '           for "Sheet4".  Since this is not the active sheet, the sheet must

     '           be defined via the passed arguement.

     '

    SheetName = "Sheet4"

    MsgBox "Worksheet name = " & SheetName & vbCrLf & _

    "First non-blank row = " & xlFirstRow(SheetName) & vbCrLf & _

    "Last non-blank row = " & xlLastRow(SheetName), vbInformation, _

    "Passed Sheet Name Demonstration"

    

    

End Sub

Function xlFirstRow(Optional WorksheetName As String) As Long

    

     '    find the first populated row in a worksheet

    

    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name

    With Worksheets(WorksheetName)

        On Error Resume Next

        xlFirstRow = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _

        xlWhole, xlByRows, xlNext).Row

        If Err <> 0 Then xlFirstRow = 0

    End With

    

End Function

Function xlLastRow(Optional WorksheetName As String) As Long

    

     '    find the last populated row in a worksheet

    

    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name

    With Worksheets(WorksheetName)

        On Error Resume Next

        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _

        xlWhole, xlByRows, xlPrevious).Row

        If Err <> 0 Then xlLastRow = 0

    End With

    

End Function

Quickly Delete Blank Rows From A Long List

1.     First, before you make any drastic changes to any workbook always make sure you have a backup copy or a recently saved copy in case you need to restore the original data .

2.     Now, select the cells in one column from the top of your list to the bottom.

3.     Make sure that all the blank cells in this selected range are the rows you want to delete.

4.     Press the F5 key on your keyboard (or select Edit, Goto).

5.     Click the Special button.

6.     Click the Blanks option and click OK. This will select all blank cells in the range you had previously selected.

7. Now choose Edit, Delete, select the Entire Row option and click OK.

VBA Code

Sub DeleteBlankRows2()

'Deletes the entire row within the selection if _

 some of the cells WITHIN THE SELECTION contain no data.

On Error Resume Next

Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete

On Error GoTo 0

End Sub

---

Sub DeleteBlankRows3()

'Deletes the entire row within the selection if _

the ENTIRE row contains no data.

Dim Rw As Range

If WorksheetFunction.CountA(Selection) = 0 Then

   MsgBox "No data found", vbOKOnly, "OzGrid.com"

   Exit Sub

End If

    With Application

        .Calculation = xlCalculationManual

        .ScreenUpdating = False

    Selection.SpecialCells(xlCellTypeBlanks).Select

        For Each Rw In Selection.Rows

            If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then

                Selection.EntireRow.Delete

            End If

        Next Rw

        .Calculation = xlCalculationAutomatic

        .ScreenUpdating = True

    End With

End Sub