This Access tutorial explains how to delete blank rows and blank cells in Excel before import into Access.
You may also want to read:
Import Excel File into Access
When you import an Excel file into Access, you may come up with several issues regarding Excel blank cells.
- Excel blank cells are not considered as Null value in Access
- Excel blank rows after the last record are also imported
- Excel blank columns after the last column are also imported
Blank cells would cause the following inconvenience in Access
- Blank Cells are counted using COUNT
- Blank columns are assigned default header names
- 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:
- Cells were used but deleted the values, the cells are still considered as “Used” and are imported blank values in Access
- 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