Fill Blank Cells In Column Range

    How do I make this to work with dynamic range instead running for complete column? For example: Run this code until last active cell in Column A. Column A will always has value so if cell A25 is last active cell in range then this code should stop executing for column R25. So execution of this code would depend on active cell in column A.


    Code
    Columns("R:R").Select                         
    Dim Cell As Range
     For Each Cell In Selection.Cells
        If Cell.Value = True Then             ' if true do nothing
            Cell.Value = ""
        ElseIf Cell.Value = False Then       ' if false change to Null
            Cell.Value = "Null"
        End If
     Next


    Any help on this would be greatly appreciated
    Thank you

    Re: Dynamic Range


    Maybe


    Code
    Dim c As Range
For Each c In Range("R1:R" & Cells(Rows.Count, "A").End(xlUp).Row)
    If c.Value = True Then ' if true do nothing
        c.Value = ""
    ElseIf c.Value = False Then ' if false change to  Null
        c.Value = "Null"
    End If
Next


    VBA Noob


    ADDED BY ADMIN

    Code
    Sub FillBlanks()
    Range("R1:R" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks) = "NULL"
End Sub

    Re: Dynamic Range


    Code
    MsgBox Range("A65536").End(xlup).Address & " is the last cell in column A."


For each oneCell in Range(Range("a1"),Range("A65536").End(xlup))
    MsgBox "Looping through the filled part of column A, the code has reached " & oneCell.Address
Next oneCell


MsgBox Application.Intersect(Range("A:A"), UsedRange).Address & " is not all of column A."

    Re: Dynamic Range


    Quote from VBA Noob

    Maybe


    Code
    Dim c As Range
For Each c In Range("R1:R" & Cells(Rows.Count, "A").End(xlUp).Row)
    If c.Value = True Then ' if true do nothing
        c.Value = ""
    ElseIf c.Value = False Then ' if false change to  Null
        c.Value = "Null"
    End If
Next


    VBA Noob



    :) Works perfect, thats exactly what i wanted. Thank you very much, I really appreciated.

    If you wish to continue using this free service.


    Please read the This Page which you agreed to when joining. In particular the point on Thread Titles. After you have done so please CLICK HERE and Private Message Dave Hawley with your acceptance, or non-acceptance of the rules. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.


    [fa]*[/fa]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login