Clearing cells with empty string values

Question

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

Have you turned off screenupdating and such?

Raystafarian
– Raystafarian

2015-03-11 15:24:39 +00:00
Commented Mar 11, 2015 at 15:24 — Raystafarian
– Raystafarian, Commented Mar 11, 2015 at 15:24
Yes, I have tried that

Shazu
– Shazu

2015-03-11 15:25:25 +00:00
Commented Mar 11, 2015 at 15:25 — Shazu
– Shazu, Commented Mar 11, 2015 at 15:25

tigeravatar · Accepted Answer · 2015-03-11 15:48:58Z

2

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

answered Mar 11, 2015 at 15:48

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wajeemba Over a year ago

This worked for me, but it did clear formulas I had. It was all I needed to clean my data because I only had one column of formulas that I pulled back in from the copy of the file I had just saved. I'll also say, it didn't work at first when I had the data in a table. I converted to a range and it handled the data nicely! Was able to convert back to a table no problem after. Thank you!

Gary's Student · Accepted Answer · 2015-03-11 16:16:02Z

1

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

edited Mar 11, 2015 at 16:16

answered Mar 11, 2015 at 15:41

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Clearing cells with empty string values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related