Excel delete blank rows and blank cells before import into Access

This Access tutorial explains how to delete blank rows and blank cells in Excel before import into Access.

You may also want to read:

Excel Delete Blank Rows

Import Chinese CSV to Excel

Import Excel File into Access

When you import an Excel file into Access, you may come up with several issues regarding Excel blank cells.

  1. Excel blank cells are not considered as Null value in Access
  2. Excel blank rows after the last record are also imported
  3. Excel blank columns after the last column are also imported

Blank cells would cause the following inconvenience in Access

  1. Blank Cells are counted using COUNT
  2. Blank columns are assigned default header names
  3. From Access perspective, Null value is different from blank value, where Null value is uninitialized while blank value is initialized. When you apply Is Null condition, you can only get the data of Null value, you need to apply condition =”” to get the initialized blank values

The above happen mainly for two reasons:

  1. Cells were used but deleted the values, the cells are still considered as “Used” and are imported blank values in Access
  2. Data generated from external systems

Excel delete blank rows and blank cells before import into Access

I created a Macro for data cleansing in Excel sheet to delete blank rows, blank columns, and blank cells. Blank rows and Blank columns in between data will not be deleted. Let me know if there are some scenarios that don’t work for you, I will look into it.

Public Sub delete_empty_row()
    Application.ScreenUpdating = False

    For Each usedrng In ActiveSheet.UsedRange
        If usedrng.MergeCells = True Then
            If usedrng.Value = "" Then
                usedrng.Value = ""
            End If
        Else
            If usedrng.Value = "" Then
                usedrng.ClearContents
            End If
        End If
    Next
    
    ActiveSheet.UsedRange
    usedRangeLastColNum = ActiveSheet.UsedRange.Columns.Count
    usedrangelastrow = ActiveSheet.UsedRange.Rows.Count
    
    For r = usedrangelastrow To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(r, usedRangeLastColNum).EntireRow) <> 0 Then
            Exit For
        Else
            Cells(r, usedRangeLastColNum).EntireRow.Delete
        End If
    Next r
    
    For c = usedRangeLastColNum To 1 Step -1
        If Application.WorksheetFunction.CountA(Cells(1, c).EntireColumn) <> 0 Then
            Exit For
        Else
            Cells(1, c).EntireColumn.Delete
        End If
    Next c
    
    ActiveSheet.UsedRange
    Application.ScreenUpdating = True
End Sub

Outbound References

https://support.office.com/en-au/article/Move-data-from-Excel-to-Access-90c35a40-bcc3-46d9-aa7f-4106f78850b4

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *