CODEWITHSUNDEEP
excelvba
Shazu
Shazu

Reputation: 587

Clearing cells with empty string values

I am trying to export an excel file to an access database. Certain columns/fields need to have field type number, because these fields will be used later on in queries involving functions like sum, count etc.

Now the problem is that the corresponding columns in excel contain empty strings that create problem while exporting to access database (field type mismatch). Even if these columns in excel have the cells formatted as numbers, the problem persists

I used the following VBA Code to eliminate all the empty strings in these columns and it works for a decent number of rows. But the excel file will have 350000 plus rows, and for such a large number of rows, the code seems to run forever.

Any tips on improving my code or taking another approach will be highly appreciated 

Dim r As Range
For Each r In ActiveSheet.Range("AG2:AJ" & lastRow)
    With r
        If .Value = "" Then
            .Clear
        End If
    End With
Next

Upvotes: 1

Views: 1914

Answers (2)

Gary's Student
Gary's Student

Reputation: 96781

If you have no formulas in the columns then running:

Sub clearTHEM()
    Dim r As Range
    Dim N As Long
    N = Cells(Rows.Count, "AG").End(xlUp).Row
    Set r = Range("AG2:AJ" & N)
    With r
        .Value = .Value
    End With
End Sub

will clear the Nulls. However it will also blast away formulas. If you have formulas, then run the macro, then export, but don't save.

EDIT#1:

For a slightly cleaner approach, this code will create a special, for export only, copy of the worksheet without either nulls or formulas:

Sub ForExportOnly()
    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "For Export Only"
    With ActiveSheet.UsedRange
        .Value = .Value
    End With
End Sub

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26660

In case you do have formulas, this should work for you and ran on a test dataset for me that had 360000 rows in less than 5 seconds:

Sub tgr()

    Dim lCalc As XlCalculation
    Dim lCol As Long
    Dim lLastRow As Long

    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    lLastRow = Range("AG:AJ").Find("*", Range("AG1"), , , , xlPrevious).Row

    For lCol = Columns("AG").Column To Columns("AJ").Column
        With Cells(1, lCol).Resize(lLastRow)
            .AutoFilter 1, "="
            .Offset(1).Resize(.Rows.Count - 1).ClearContents
            .AutoFilter
        End With
    Next lCol

    With Application
        .Calculation = lCalc
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Upvotes: 2

Related Questions