Select Range in Excel and replace Nulls

Question

I am working on a workbook that imports data from Access to Excel and need to figure out how to replace values in certain columns with a default date value when the cell is null. I have 24 columns that are not ordered next to each other that have date values in them, but there are blanks. I was attempting to use a select range code to select the range and replace the value, but I run into issues with the range isn't contiguous. I was thinking that I could use another column that is almost always filled out to identify the end of the range count and continue to do the null replacement that I started with but I don't know how to do that. Is there a way to accomplish this easily? Here are the codes that I am working with now:

Sub replace_blanks()
    Dim cellvar As Range
    For Each cellvar In Selection.Cells
        If cellvar.Value = vbNullString Then
            cellvar.Value = "1/1/1900"
        End If
    Next cellvar
End Sub

and

Sub AfterImport()

    Range("B1", Range("B1").End(xlDown)).Select

End Sub

Update:

Sub AfterImport()
Dim cellvar As Range
Dim AccessData As Worksheet
Dim lngLastRow As Long

Set AccessData = Worksheets("AccessData")
With AccessData

    lngLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    Range("B1", "B" & lngLastRow).Select
        For Each cellvar In Selection.Cells
            If cellvar.Value = vbNullString Then
             cellvar.Value = "1/1/1900"
            End If
        Next cellvar
End With

Note: This last section of code works when the active sheet is the AccessData sheet. However, when I run it on another sheet I get an error and the macro quits.

Thank you.

`Range.Areas` gives you the non-contiguous "groups" of cells. — Mathieu Guindon, Sep 06 '18 at 19:23
First get your `Selection` into a `Range` variable, then iterate that variable's `Areas`, then iterate each area's `Cells`. — Mathieu Guindon, Sep 06 '18 at 19:44
I don't know what you mean. Can you show me an example? Also, I updated my original question with the ranges that I need to iterate through. Thank you. — JosephD, Sep 06 '18 at 19:57
Maybe fixing on import is an easier option. How do you import? — ComputerVersteher, Sep 06 '18 at 23:00
Is the value of "Unknown" the same for all the columns? If so, you can find the bottom row number with a formula like `Cells(Rows.Count, 1).End(xlUp).Row` then use that to define your ranges instead of a selection. In general it's best to avoid using Selection if possible. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — DavidP, Sep 07 '18 at 20:08
I updated my post to include the code that I have been using, which works when I am on the tab that data is importing to, but the macro is initiated from another tab which causes an error to show. I need to be able to initiate this code from a main worksheet and have it update the `AccessData` worksheet. Advice? — JosephD, Sep 07 '18 at 22:52
I added a `worksheets("AccessData").Activate line after the code to set the worksheet as the active sheet and that resolves the problem. This is a closed questions. Thank you everyone for your help. — JosephD, Sep 07 '18 at 23:49

score 0 · Answer 1 · answered Sep 07 '18 at 23:51

Here's the completed code:

Sub AfterImport()
Dim cellvar As Range
Dim AccessData As Worksheet
Dim lngLastRow As Long

Set AccessData = Worksheets("AccessData")
With AccessData

    lngLastRow = .Cells(.Rows.Count, "D").End(xlUp).Row

End With
worksheets("AccessData").Activate
Range("B1", "B" & lngLastRow).Select
        For Each cellvar In Selection.Cells
            If cellvar.Value = vbNullString Then
             cellvar.Value = "1/1/1900"
            End If
        Next cellvar
End Sub

Select Range in Excel and replace Nulls

1 Answers1