Revised vba to fill blanks in a range

kumarsatish · Oct 5th 2019

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]

**Carim** · Oct 5th 2019

Hello,

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

kumarsatish · Oct 5th 2019

File attached for your necessary perusal & action please

**Carim** · Oct 5th 2019

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 ...

kumarsatish · Oct 6th 2019

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.

**Carim** · Oct 6th 2019

Quote from kumarsatish

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

**Carim** · Oct 6th 2019

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

Code

Private Sub Worksheet_Calculate()
Dim c As Range


'  Delete data in F2:G42 if cell A2 ="z"
    If Range("A2") = "z" Then Range("F2:G42").ClearContents : Exit Sub
    
'   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 c In Range("C2:C42")
'       Check/update Maximum
        If (Len(c.Offset(0, 3)) > 0) And (IsNumeric(c.Offset(0, 3))) Then
            If c > c.Offset(0, 3) Then c.Offset(0, 3) = c
        Else
            c.Offset(0, 3) = c
        End If
'       Check/update Minimum
        If (Len(c.Offset(0, 4)) > 0) And (IsNumeric(c.Offset(0, 4))) Then
            If c < c.Offset(0, 4) Then c.Offset(0, 4) = c
        Else
            c.Offset(0, 4) = c
        End If
    Next c
    
    Application.EnableEvents = True


End Sub

Display More

Hope this will help

kumarsatish · Oct 7th 2019

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.

**Carim** · Oct 7th 2019

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

**Carim** · Oct 8th 2019

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

kumarsatish · Oct 9th 2019

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

**Carim** · Oct 9th 2019

Excellent ...

Waiting for your feedback ...

kumarsatish · Oct 10th 2019

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

**Carim** · Oct 10th 2019

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

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

kumarsatish · Oct 10th 2019

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.

**Carim** · Oct 10th 2019

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

Hope this will help

kumarsatish · Oct 10th 2019

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

I had downloaded the Version 2 & tried to Run as it is i.e. WITHOUT ADDING/MODIFYING ANYTHING

**Carim** · Oct 10th 2019

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 ...

kumarsatish · Oct 11th 2019

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:

**Carim** · Oct 11th 2019

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

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

Revised vba to fill blanks in a range

Participate now!

Share