CODEWITHSUNDEEP
vbaexcel
Jordan Harris
Jordan Harris

Reputation: 131

Auto-filling a column with data once one cell has been updated

How can I auto-fill a column with 0's once one cell in the column has been filled?

Example:

Original Table

Original Table

Once I enter a number into any cell in the April Column... enter image description here

I want the column to auto-fill the rest of the cells with 0's, like this... enter image description here

My first idea was to use the Worksheet_Change event like so...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("H6:H16") 'H6 to H16 is the range of the April column, I would repeat this for each column in a loop

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    Range("H6:H16").Value = 0 
End If
End Sub

But this overwrites the entire column, while I want to keep the original value.

Is there some way for Excel to return exactly what cell was changed so that I can change the value of all cells in that column except for that one? Or is there another easier way to do this?

Upvotes: 1

Views: 171

Answers (3)

rwilson
rwilson

Reputation: 2145

Just thinking outside the box for a possible non-vba way of going about doing this. You could first fill all the cells with zeros:

enter image description here

Then starting at A2, highlight the range "A2:L12" and go to Home-->Conditional Formatting-->New Rule-->Use a formula to determine which cells to format and insert the formula:

=COUNTIF(A$2:A$12,">0")=0

enter image description here

Then go to Format-->Font and change the font color to white (Another alternative, and probably a better one, would be to change the Number Format to Custom with a Type of ;;; as suggested by Jeeped).

enter image description here

After you press OK a few times and have exited the conditional formatting rules manager, your sheet should look like this:

enter image description here

Once you add a value greater than zero to a cell, you will see all the zeros in the column appear.

enter image description here

This approach may not be acceptable for what you are trying to do since the value you enter has to be greater than zero, but I thought I would post it in case it is something you feel you can use.

Upvotes: 4

Elbert Villarreal
Elbert Villarreal

Reputation: 1716

Heres is my answer, hoping to give some help.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r
    Dim c
    Dim keyCells As Range
    Dim i

    r = Target.Row
    c = Target.Column
    'January in column E = 5
    'December in column P = 16
    Set keyCells = Range(Cells(6, c), Cells(16, c))

    If c >= 5 And c <= 16 Then ' if target is between the columns
        If r >= 6 And r <= 16 Then ' if the target is between the rows
            For Each i In keyCells
                If Not i.Address = Target.Address Then
                    Application.EnableEvents = False
                    i.Value = 0
                    Application.EnableEvents = True
                End If
            Next i
        End If
    End If
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

With Apr in H6:H16, your data range would seem to be in E6:P16.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E6:P16")) Is Nothing Then
        On Error GoTo bm_Safe_exit
        Application.EnableEvents = False
        Dim tgt As Range, var As Variant
        For Each tgt In Intersect(Target, Range("E6:P16"))
            With Cells(6, tgt.Column).Resize(11, 1)
                If Application.Count(.Cells) = 1 Then
                    var = tgt.Value
                    .Cells = 0
                    tgt = var
                End If
            End With
        Next tgt
    End If
bm_Safe_exit:
    Application.EnableEvents = True
End Sub

Always disable event handling before writing values to the worksheet or you will trigger another Worksheet_Change event macro that runs on top of the original possibly triggering a cascade of events.

Upvotes: 1

Related Questions