# 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 "Smiley" icon, below, in the bottom right corner

## Files

• 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 "Smiley" icon, 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
``````'  Delete data in F2:G42 if cell A2 ="z"
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 "Smiley" icon, 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 "Smiley" icon, 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
``````Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'  Delete data in F2:G42 if cell A2 ="z"
If Sheet81.Range("A2") = "z" Then Sheet81.Range("F2:G42").ClearContents
End Sub``````

Hope this will help

## Files

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

• Once you have tested the macro ... feel free to share your comments

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

• Certainly....I would be testing it again after 8 hours & give you the feedback immediately.

• Excellent ...

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

• I tested it but some error was there....I'm still testing.....would give you the feedback CERTAINLY

• 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 "Smiley" icon, below, in the bottom right corner

• Carim
I tried again but 'probably' I am making mistake in 'placing' code PROPERLY. Would be happy if you can insert the code in the Test file & attach it.

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

Hope this will help

## Files

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

• I tried to Run the Code by hitting 'debug' button...BUT it showed
Run-time error '9':
Subscript out of range

• 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 "Smiley" icon, 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 "Smiley" icon, below, in the bottom right corner

## Participate now!

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