Revised vba to fill blanks in a range

  • I am counting medicine’s NUMBER OF rejections of ‘2 kinds’ in F2:G42 using vba. This I am able to achieve.


    Before closing the Excel Workbook (A2 becomes equal to z then), I want to fill blanks again in F2:G42 (using REVISED vba) for next calculations to remain ready.


    I am using 3 Worksheets in the SAME Workbook (ws1, ws2 & ws3) with above data & want the same exercise to happen in all the 3 Worksheets.


    A2 becomes equal to z in all the 3 Worksheets at the SAME TIME.


    I want to get a “REVISED VBA” to fill blanks in a data range (F2:G42) whenever a particular condition is fulfilled (A2=z). The same data range is filled with values when ‘another’ condition is fulfilled (A1=1) & this I am achieving with the help of VBA as given below


    [VBA]
    Private Sub Worksheet_Calculate() Dim cell As Range ' Exit if A1 not equal to 1 If Range("A1") <> 1 Then Exit Sub Application.EnableEvents = False ' Loop through range of values that is being updated For Each cell In Range("C2:C42") ' Check/update Maximum If (Len(cell.Offset(0, 3)) > 0) And (IsNumeric(cell.Offset(0, 3))) Then If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell Else cell.Offset(0, 3) = cell End If ' Check/update Minimum If (Len(cell.Offset(0, 4)) > 0) And (IsNumeric(cell.Offset(0, 4))) Then If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell Else cell.Offset(0, 4) = cell End If Next cell Application.EnableEvents = True End Sub [/VBA]

  • Hello,


    To make everything easier and clearer for everybody ... you should attach your workbook ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    Thanks for the test file


    A couple of remarks :


    1. In your three Sheets : ws1, ws2 and ws3 ... there are No Blank Cells in the reference Range F2:G42 ...


    2. Should you have Blank cells ... you are not indicating with what Source Data should these Blank cells be filled ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I’ll answer you to make you more ‘clear’

    • Before I close the Workbook, A2=”z” at that moment & REMAINS “z” till the Workbook is closed, I MANUALLY make F2:G44 as blanks in all 3 Worksheets ONLY when I VISUALLY see A2=”z” (by selecting F2:G44 & hitting ‘Delete’ button)……so the F2:G44 are blanks when the Workbook is re-opened next day when A1=0 & A2=””.
    • Column C gets ‘real time updating’ when A1=1; eventually thereafter F2:G44 are AUTOMATICALLY FILLED with values. This action I am able to achieve successfully using vba as in the module. The source data is in another Worksheet (ws5) of this same Workbook (which gets real time feed from a software, hence I was unable to insert ws5). Column C contains formulas & which gets values from ws5.
    • My requirement is: when A2 becomes equal to “z”, values in F2:G44 should get deleted AUTOMATICALLY & GET FILLED WITH BLANKS.

  • My requirement is: when A2 becomes equal to “z”, values in F2:G44 should get deleted AUTOMATICALLY & GET FILLED WITH BLANKS.


    You can add following instruction :


    Code
    1. ' Delete data in F2:G42 if cell A2 ="z"
    2. If Range("A2") = "z" Then Range("F2:G42").ClearContents


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • As far as your event macro... you can modify it as follows ...



    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • I tried to RUN the code by pasting it in ONLY 1 Worksheet ws1 & by hitting 'Debug' button...........IT MAKES THE SYSTEM HANG.............Though it filled blanks in F2:G42...some more modification may be required in the code.

  • Hello,


    You can test following macro to be stored in ThisWorkBook module :


    Code
    1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    2. ' Delete data in F2:G42 if cell A2 ="z"
    3. If Sheet81.Range("A2") = "z" Then Sheet81.Range("F2:G42").ClearContents
    4. End Sub


    Hope this will help

    Files

    • Test SH V1.xlsm

      (32.44 kB, downloaded 47 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Understand you are transposing macro to your real-life workbook ...


    However, you could perform simulations with the test file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Attached is Version 2 with the fully operational macro ... which is stored in ThisWorbook module ...


    Hope this will help

    Files

    • Test SH V2.xlsm

      (31.86 kB, downloaded 50 times, last: )

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • When it comes to testing an event macro ...


    You should first manually fill in some cells in range F2:G42 ...


    While making sure "z" appears in cell A2 ...


    Just Save the file ...


    Before the Save gets executed ... all your cells will be replaced by blanks ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • IT WORKS….Your Code Works……..I am so happy that ……..unable to express now…….that little modification in the A2=”z” has to be done because……….I’ll tell you tomorrow…….Currently overwhelmed because of you…..EXICITED…….HAPPY…..Thank you CARIM :wowee::wowee::wowee:

  • Glad you have managed to test the macro ...:wink:


    Thanks for your Thanks ... AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)