Delete blank rows from excel


  • Hello Developers,

    I am trying to import data from excel into access using transferspreadsheet method but issue in in excel i only have 5000 rows and my vba code imports 47000 rows with 42000 blank records. How shall i get rid of this blank rows in excel.

    I have below code in access to import excel data

    Sub ImpFrmtXL()
    Dim LTotal As Long
    DoCmd.SetWarnings False
    Set objapp = CreateObject("Excel.Application")
                objapp.Visible = False
                Set wb = objapp.Workbooks.Open("L:\NEW\REAL.xlsx",true, false)
        Selection.NumberFormat = "@"
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "tblREAL", "L:\NEW\REAL.xlsx", True
    Set objapp = Nothing
    DoCmd.SetWarnings True
    End Sub

    • Edited by zaveri cc Wednesday, December 18, 2013 5:14 PM
    Wednesday, December 18, 2013 5:10 PM


  • You're talking about a database, which are not meant to have blanks. You can have NULL values, but blanks should not be allowed by your data rules.

    Anyway, try this:

    Dim r As Long
    r = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Rows(r + 1).Resize(ActiveSheet.UsedRange.Rows.Count + 1 - r).Delete

    Thursday, December 19, 2013 3:20 PM

All replies