When it comes to the programming, I'm not sure where to begin. I have an Excel column of data with an array that is dispersed with non-consecutive zeros. I want to create a new column of data and choose the digit that comes before the zeros. A sample data set is provided below (I'll select out 1, 4, and 6):

```  5
1
0
4
0
820
4
6
0
74
```
```    Sub Cat()

Range("V12").Select

Do Until IsEmpty(ActiveCell)

ActiveCell.Offset(1, 0).Select
Loop

End Sub```
Mar 23 in Others 82 views

## 1 answer to this question.

Please attempt the next option. It is assumed that A contains the range that needs to be processed. A column's content will be dropped into the resulting array in B:B, starting with B1:

```Sub ExtractBeforeZero()
Dim sh As Worksheet, lastR As Long, arr, arrB0, i As Long, k As Long

Set sh = ActiveSheet
lastR = sh.Range("A" & sh.rows.count).End(xlUp).row

arr = sh.Range("A1:A" & lastR).Value2 'place the range in an array for faster  processing
ReDim arrB0(1 To UBound(arr)) 'redim the array to keep the return of its maximum possible number of elements
k = 1

For i = 1 To UBound(arr)
If arr(i, 1) = 0 Then arrB0(k) = arr(i - 1, 1): k = k + 1
Next i

If k > 1 Then 'if at least a zero has been found:
ReDim Preserve arrB0(1 To k - 1) 'keep only the filled elements
'drop the resulted array:
sh.Range("B1").Resize(UBound(arrB0), 1).Value2 = Application.Transpose(arrB0)
Else
MsgBox "No zero could be found in the processed column..."
End If
End Sub```
• 63,040 points

## Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

## SQL Group By with an Order By

I have a table of tags and ...READ MORE

## How to print an Excel Sheet using VBA with Nitro PDF Creator in Excel 2016

you can use the built-in excel facilities ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE