# Filling zeros between zeros

#### johngio

##### Board Regular
Hi all,

I have 2 columns as follows:

1200___1800
0______0
1200___1800
1200___1800
1200___1800
1200___1800
0______0
1200___1800

And I want to make the numbers between the zeros zero also (so it looks like:
1200___1800
0______0
0______0
0______0
0______0
0______0
0______0
1200___1800

I created the following macro:

Sub replace()

Dim iRow As Integer, iCol As Integer
For iRow = 16 To 279
For iCol = 17 To 18
If Cells(iRow, iCol) <> 0 And Cells(iRow - 1, iCol) = 0 Then
Cells(iRow, iCol) = 0
Else
Cells(iRow, iCol) = Cells(iRow, iCol)
End If
Next iCol
Next iRow

End Sub

The problem I am having is everthing below the first zero becomes zero (which makes sense as it would see any non zero with a zero above and make it a zero).

Any ideas how to alter this macro to make it look for the first occurence of a zero value, and then make everthing in the 2 columns zero until it hits the next zero, and then leave it unchanged?

Cheers

John

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### acw

##### MrExcel MVP
John

Try

Code:
``````Sub ddd()
Dim MakeZero As Boolean
MakeZero = False

For Each ce In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If ce.Value = 0 Then MakeZero = Not (MakeZero)
If MakeZero Then
ce.Value = 0
ce.Offset(0, 1).Value = 0
End If
Next ce
End Sub``````

Tony

#### johngio

##### Board Regular
Hmmm.

Not working. I tried setting it to the first cell in the list (Q16) but for some reason it zeros the cells above.

What does the Makezero = Not (Makezero) do ?

Cheers

John

#### acw

##### MrExcel MVP
John

1) Where is the data you want to test.
2) What code are you using (ie how have you changed the code above)
3) MakeZero is a boolean variable. The line
makezero = not(makezero) will flip it from the existing to the opposite. So if it is currently true, it will become false; if false it will become true.

Tony

#### johngio

##### Board Regular
I will try mucking around with a loop within a loop. Such as
If cells(iRow,iCol) = 0 Then
keep making the next rows/columns zero until hit a zero then exit loop

#### Macropheliac

##### Board Regular
Maybe this:

Code:
``````Sub Zeros()
Dim MyCell As Range
Dim MyCell2 As Range

Set MyCell = Range("A1")
Set MyCell2 = Range("B1")

Do Until MyCell.Value = "0"
Set MyCell = MyCell.Offset(1)
Loop

Do Until MyCell.Offset(1).Value = "0"
MyCell.Offset(1).Value = "0"
Set MyCell = MyCell.Offset(1)
Loop

Do Until MyCell2.Value = "0"
Set MyCell2 = MyCell2.Offset(1)
Loop

Do Until MyCell2.Offset(1).Value = "0"
MyCell2.Offset(1).Value = "0"
Set MyCell2 = MyCell2.Offset(1)
Loop

End Sub``````

It's a little loopy, but it worked in a test.

Mac

Woohoo!!!!

Thats the one.

Thanks!

Replies
1
Views
426
Replies
1
Views
2K
Replies
7
Views
935
Replies
0
Views
1K
Replies
6
Views
277

1,172,169
Messages
5,879,442
Members
433,431
Latest member
LikeablePringle

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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