none
Delete blank rows from excel

    Question

  • 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)
                Columns("A:A").Select
        Selection.NumberFormat = "@"
            ActiveWorkbook.Save
        ActiveWorkbook.Close
        
        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

Answers

  • 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

  • Just sort the entire sheet based on an appropriate value, either ascending or descending, and the blanks will go to the bottom.
    Wednesday, December 18, 2013 6:21 PM
  • blanks will go to the bottom but still they get imported into access as blank which i don't want.
    Wednesday, December 18, 2013 9:57 PM
  • Then add code that selects the blanks in the column and deletes the rows - if you are sorting on column A, use

    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    Wednesday, December 18, 2013 10:13 PM
  • This will delete the row with only one cell blank? If yes than i don't want to delete that, i only want to delete if entire row is blank.
    Thursday, December 19, 2013 2:31 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
  • This works.

    Thanks.

    Thursday, December 19, 2013 3:37 PM