VBA for setting cell content to Zero

[sondayl]

I have a budgeting spreadsheet with multiple columns. I am referencing 3 columns with invoice amounts from the Previous Fiscal Year, and 3 columns with invoice amounts from the Current Fiscal Year. The Columns with previous FY information in them are C, D & E, the Current FY amounts are in F, G & H. I would like for the columns row in C3, C4, C5, Etc and D2, D4, D5, Etc and E3, E4, E5, etc to be deleted once I type in new invoice information in F3, G3, H3. etc.

I was trying to a .clearcontents VBA script but it doesn't want to seem to work. I created a Macro as an example, but am having trouble making the ranges.

Sub UpdateContents()
'
' UpdateContents Macro
'
' Keyboard Shortcut: Ctrl+u
'
Range("G9").Select
ActiveCell.FormulaR1C1 = "117.369"
Range("C9").Select
Selection.ClearContents
Range("D9").Select
Selection.ClearContents
Range("E9").Select
Selection.ClearContents
End Sub

 

[Joe4]

Welcome to the Board!

Your code would populate G9 with "117.369" and clear the contents of C9:E9.
Is that not doing what you want? Or what exactly seems to be the issue?

Note, your code could be simplified to this:

Sub UpdateContents()

Range("G9") = "117.369"
Range("C9:E9").ClearContents

End Sub

 

[sondayl]

HI Joe4,
I do want that (that is a macro that I created for that line), I was hoping to apply to the rest of the rows in the spreadsheet to set it up automatically, and do not how to rewrite the code to accomplish that.

 

[Joe4]

Can you explain, just is plain English, exactly what you want to happen?
Do you want this code to run automatically, or are you going to run it manually?
Which range of cells do you want it to run against, exactly (all rows, just certain rows)?

 

[sondayl]

Hi Joe4,

Thank you for replies, I was able to ask a friend and I was going down the wrong rabbit hole. I needed a Do Loop script, I have it done now. Thank you!

 

[Joe4]

Hi Joe4,

Thank you for replies, I was able to ask a friend and I was going down the wrong rabbit hole. I needed a Do Loop script, I have it done now. Thank you!

Would you mind posting your solution?

Note that loops are very inefficient, and many times are completely unnecessary. But without having a complete understanding of how exactly how you want this to work, it is difficult for me to give you the exact code you need.

 

[sondayl]

Please let me know if this can simplified ...

Sub UpdateContents()

'UpdateContents Macro
' Select the cell with the first row of data - **UPDATE THIS IF IT CHANGES
Range("A3").Select
Dim myRows As Integer
myRows = 3 'starting row number
'Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
If IsEmpty(ActiveSheet.Cells(myRows, 7)) = False Then
'MsgBox "Current FY 806 GL Value in Row " & myRows & " is updated" & vbNewLine & "Deleting prior FY amounts."
ActiveSheet.Cells(myRows, 4).ClearContents
ActiveSheet.Cells(myRows, 5).ClearContents
End If

ActiveCell.Offset(1, 0).Select
myRows = myRows + 1
Loop
End Sub

 

[Joe4]

If your goal is to clear columns 4 and 5 from rows 3 to the last row with data in column 7, then this should do it:

Sub UpdateContents()

    Dim lr As Long
    
'   Find last row in 7th column with data
    lr = Cells(Rows.Count, 7).End(xlUp).Row
    
'   Clear data from columns 4 and 5 from all rows from row 3 to last row
    Range(Cells(3, 4), Cells(lr, 5)).ClearContents

End Sub

 

[sondayl]

Hi Joe4,
Yes! this is exactly what I needed, thank you so much your help. One more question with this, do I have to use a macro shortcut key in order to execute the script, or will it run automatically once I save and exit?

 

[Joe4]

No, this is not automatic. Only event procedures run automatically. Just drop the body of the code in a BeforeSave event procedure.
See: Excel VBA Examples: Introductory Event Examples - dummies