Hi Tina,
welcome to to the forum.
I can't quite follow your comment - "the numbers who are greater than 0 can have the same number of rows of zero", but try the following formula
=IF(A1>0,A1,A1+1). It will convert col 1 to col 2 based on the pattern of your numbers.
FarmerScott
That's where the question got me at first. I believe that the poster is starting with a series of whole numbers greater than zero. The intent, I believe, is to insert X # of rows beneath each row where X = the value in the cell.
If this is the intent, and the numbers only go up to "5" then a macro can be written with a shortcut that you could use (providing the list isn't terribly long). here just paste this macro in and use control + z with the cell highlighted.
Then replace the empty cells in column A with "0" and then paste the function =IF($A1>0,$A1,1) into column B
Sub Macro()
'
' Macro Macro
'
' Keyboard Shortcut: Ctrl+z
'
If ActiveCell = "1" Then
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
If ActiveCell = "2" Then
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
If ActiveCell = "3" Then
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
If ActiveCell = "4" Then
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
If ActiveCell = "5" Then
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End If
End Sub