# Create a sequece from 1 to whatever accross non-contiuous/contiguous cells w/in one column

• ### Question

• OK, I hope I describe this correctly.

What I would like to do is to fill a series of numbers in column Z based upon weather there is a value in column E.  Keep in mind that values in Column E have blank cells between them which makes filling a series in excel not work.

Starting at row 9 look at column E if there is a value in that Row/Column then enter a numeric value in the same row (9) and column Z starting with one.  Go to the next row if there is a value in column E and row 10 then increment and make column/row Z10 number 2 and so on until the end of data (based upon last row on column K is reached).  I normally would research but don't have time right now as I am swamped with work, and this is on of my hold ups.  Manually doing this makes for a lot of mistakes.

Regards,

Neil

Jonnybrovo815

Wednesday, January 20, 2016 5:22 PM

• Hi

Try this macro..

Sub test()

Dim lst As Long, i As Integer, tsl as long
lst = Cells(Rows.Count, 5).End(xlUp).Row
tsl = Cells(Rows.Count, 25).End(xlUp).Row

For i = 9 To lst

If Cells(i, 5) = "" Then
Cells(i, 5).Offset(0, 21) = tsl

tsl = tsl + 1
End If
Next i

End Sub

Cimjet

• Edited by Thursday, January 21, 2016 4:52 PM
• Proposed as answer by Friday, January 22, 2016 5:14 PM
• Marked as answer by Friday, January 29, 2016 10:09 PM
Thursday, January 21, 2016 4:48 PM
• OK this is what I ended up with and it works for my purposes.  I had to change it a bit to add a section to remove any existing values in column Z.  Then I had to make sure it only went to the bottom of my data based upon column K or the last filled in cell in column K before a blank row.  It is in escence what Cimjet provided with some tweeks to make it work exactly how I needed it to work.  Also I decided to go with a base 10 sequence instead of by ones.

Sub PkgSq()

Dim i As Integer
Dim tsl As Long
Dim LastRow As Long

LastRow = Worksheets("NCSA_ISS_ITEM_BOM").Range("K9").End(xlDown).row

'wipe column 21 row 9 to endrow
Range("Z9:Z10000").ClearContents

tsl = 10

For i = 9 To LastRow

If Cells(i, 5) <> "" Then
Cells(i, 5).Offset(0, 21) = tsl

tsl = tsl + 10
End If
Next i

End Sub

Jonnybrovo815

• Marked as answer by Friday, January 29, 2016 10:09 PM
Friday, January 29, 2016 10:08 PM

### All replies

• Hi Neil,

First, to find the last row on column K is reached, we could use Range.End property. For example:

``` Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "K").End(xlUp).Row
End With
MsgBox LastRow```

Secondly, for increment logical, we just could define a variable for increment (add 1 if after set value to Z).

To move cell/range, we could use Range.Offset property, so you could iterate cells in column E according to the row number (first point result) to check the value and set value.

Regards

Starain

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Thursday, January 21, 2016 9:42 AM
• Hi

Try this macro..

Sub test()

Dim lst As Long, i As Integer, tsl as long
lst = Cells(Rows.Count, 5).End(xlUp).Row
tsl = Cells(Rows.Count, 25).End(xlUp).Row

For i = 9 To lst

If Cells(i, 5) = "" Then
Cells(i, 5).Offset(0, 21) = tsl

tsl = tsl + 1
End If
Next i

End Sub

Cimjet

• Edited by Thursday, January 21, 2016 4:52 PM
• Proposed as answer by Friday, January 22, 2016 5:14 PM
• Marked as answer by Friday, January 29, 2016 10:09 PM
Thursday, January 21, 2016 4:48 PM
• Thanks Cimjet

This almost works but it is filling in the values where there are no values in column 5.  It should fill in only where there are values in column 5.  I was looking briefly for a solution or answer to the opposite of "" and didn't come up with anything quickly.  Do you know what that values is?  Is it " "?

Jonnybrovo815

Thursday, January 28, 2016 10:49 PM
• Hi Jonnybrovo815,

To check the cell value isn’t empty, please use If Cells(I,5)<>”” Then.

Regards

Starain

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.

Friday, January 29, 2016 2:00 AM
• Hi

I'm not having that problem, see link below, it's a zip file with what I understand is your requirement.

Cimjet

Friday, January 29, 2016 1:08 PM
• OK this is what I ended up with and it works for my purposes.  I had to change it a bit to add a section to remove any existing values in column Z.  Then I had to make sure it only went to the bottom of my data based upon column K or the last filled in cell in column K before a blank row.  It is in escence what Cimjet provided with some tweeks to make it work exactly how I needed it to work.  Also I decided to go with a base 10 sequence instead of by ones.

Sub PkgSq()

Dim i As Integer
Dim tsl As Long
Dim LastRow As Long

LastRow = Worksheets("NCSA_ISS_ITEM_BOM").Range("K9").End(xlDown).row

'wipe column 21 row 9 to endrow
Range("Z9:Z10000").ClearContents

tsl = 10

For i = 9 To LastRow

If Cells(i, 5) <> "" Then
Cells(i, 5).Offset(0, 21) = tsl

tsl = tsl + 10
End If
Next i

End Sub

Jonnybrovo815

• Marked as answer by Friday, January 29, 2016 10:09 PM
Friday, January 29, 2016 10:08 PM