Add next sequence number in next blank row cell only..Excel VBA program

A

Akash030193

New Member
Joined
Apr 28, 2019
Messages
22
Hello! I need your help in below query.

I am looking for a VBA program that help me to add next sequence number in next blanks row cell only (one cell only). For example, 5th row is blank cell, i need to place next sequence number as "3". Similarly in the same column, refer row 9, i need to place next sequence number as "3"... similarly for all subsequent series..

1) Question
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Revision[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

After applying macro
[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Revision[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


2) Second Example

Question

[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


After applying macro

[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Code to be run if there is any next blank row in the column "A", and to be run for one next cell only.



https://1drv.ms/x/s!AgoBOBbQ2MyJgQcKF83--PVdg_3j



****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">A1Revision2031425
6071829
10011112213
14015116217
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Hi. Try:
Code: 
Sub AddNextInBlanks()
 With Range("A2:A" & Cells(Rows.Count, 1).End(3).Row + 1)
  .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C+1"
  .Value = .Value
 End With
End Sub
 
Upvote 0
You can also do it directly without adding and then replacing formulas...
Code: 
[table="width: 500"]
[tr]
	[td]Sub AddNextInBlanks()
 With Range("A2:A" & Cells(Rows.Count, 1).End(3).Row + 1)
  .Value = Evaluate("IF(" & .Address & "=""""," & .Offset(-1).Address & "+1," & .Address & ")")
 End With
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Thanks Rick,

This program is also working well. Thank you once again for your support. I am grateful to you......:smile::smile::smile:

Regards,
Akash Patel
 
Upvote 0
In continuation to thread message, can you please provide macro program as per below table?

I want to add next sequence number in below table with prefix "R". If my cell value is R1, then after in next first blank cell it needs to be R2...vice verse.....
Question
[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]R1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]R1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Answer
[TABLE="class: grid, width: 10, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]R1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]R2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]R1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]R2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]R1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]R0[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]R1[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Akash030193 said:
In continuation to thread message, can you please provide macro program as per below table?

I want to add next sequence number in below table with prefix "R". If my cell value is R1, then after in next first blank cell it needs to be R2...vice verse.....
Click to expand...
Give this macro a try...
Code: 
Sub FillBlankWithOneMoreThanPreviousRnumber()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(Ar.Count).Offset(1).Value = "R" & Mid(Ar(Ar.Count), 2) + 1
  Next
End Sub
 
Upvote 0
Rick,

Can you share simple macro program in which I can fill blank cell with some text. For example,

Where there is blank cell in column A, I can fill blank cell with Text as per below tables.

[TABLE="class: grid, width: 20, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Carrot[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: outer_border, width: 20, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Not allocated[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Not allocated[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Not allocated[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Carrot[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Akash030193 said:
Rick,

Can you share simple macro program in which I can fill blank cell with some text. For example,

Where there is blank cell in column A, I can fill blank cell with Text as per below tables.
Click to expand...
Assuming the blanks are real blanks (not formulas displaying ""), give this macro a try...
Code: 
Sub NotAllocated()
  Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks) = "Not allocated"
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lambda and custom sequence with interference of string
Replies
3
Views
223
StephenCrump
S
R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
519
Rnkhch
R
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
218
shg
shg
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs
A
  • Solved
Define multiple phone number patterns to one RegEx.
Replies
5
Views
143
JEC
JEC

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

Share this page

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?

Disable AdBlock

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
Back
Top