# Filling Blank Cells

#### Learning Excel

##### Board Regular
Hi,
I have a workbook with a number of sheets.
I am trying to fill up some cells that are blank and have this crude code.
However, the macro will only work if I go to each sheet and run the macro
which this is not what I want.

I have save this macro in a module and would hope that it can run fill
up all the sheets that have blank cells as in the ranges when the macro is run

Hope some guidance on this.

Sub FillBlank()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In Worksheets
If Range("B36") = "" Then Range("B36") = 0
If Range("C36") = "" Then Range("C36") = 0
If Range("D36") = "" Then Range("D36") = 0

If Range("B43") = "" Then Range("B43") = 0
If Range("C43") = "" Then Range("C43") = 0
If Range("D43") = "" Then Range("D43") = 0

If Range("B50") = "" Then Range("B50") = 0
If Range("C50") = "" Then Range("C50") = 0
If Range("D50") = "" Then Range("D50") = 0

If Range("B57") = "" Then Range("B57") = 0
If Range("C57") = "" Then Range("C57") = 0
If Range("D57") = "" Then Range("D57") = 0

Next ws

End Sub

Thank
Note : I only want to fill the above ranges which is blank and not any other rows although they may be blank

NB: I have to fill the cells because I have many workbooks which I need to
extra data and this blank cells are giving me problem row offset to paste
the data and would fill up incorrectly due to the blank cell

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
try:

Sub FillGaps()

Dim ws As Worksheet

Dim i As Byte
Dim j As Byte

For Each ws In ActiveWorkbook.Worksheets

For j = 36 To 57 Step 7

For i = 2 To 4
If ws.Cells(j, i) = "" Then ws.Cells(j, i) = 0
Next i

Next j

Next ws

End Sub

HTH

Hi,
It is working as intended. Thank you for your time and help.

I do not know why my code (Filling blank) does not cycle through each
sheet to fill the blank and your is working although I have changed mine
from For Each ws In Worksheets to For Each ws In ActiveWorkbook.Worksheets

Should not the code For Each ws in ... Next ws cycle through all the sheets?

Appreciate some enlightenment.
Learning Excel

If ws.Range("B36") = "" Then ws.Range("B36") = 0

You were doing it "for" each worksheet - counting some loops - but not "on" each worksheet.

you can see that in the code amigos provided:
If ws.Cells(j, i) = "" Then ws.Cells(j, i) = 0

kind regards,
Erik

Hi,
Thank you for the enlightenment. Macro is so "abstract"... for me.

Learning Excel

You're welcome,

To make it less abstract you can step through it to SEE what happens.
Click in your code, put the "codescreen" (Visual Basic Editor) aside and hit F8, look at your screen what's happening.
To run to a certain point click in that line and hit F9, then F5 to run...

You can find all this in the menu "Debug" (I hope that's the right English terminology)

kind regards,
Erik

Replies
2
Views
346
Replies
8
Views
290
Replies
10
Views
309
Replies
14
Views
481
Replies
5
Views
442

### Forum statistics

1,206,830
Messages
6,075,109
Members
446,122
Latest member
sambee66 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back