Shifting a cell based on a string value in a column

sekar_r24 · Apr 17th 2020

HI.,

i am having a column (assume in B Column) with values of different string. I would like to find out string in the range ex: _formatversion = 1.0 and shift 3 rows down wherever the occurrences are found. i tried this below code. but unsuccessful.

Could anyone help.

Code

Sub MoveCell()
 Dim MyRange, cell As Range
 Set MyRange = ActiveSheet.Range("B1", Range("B65536").End(xlUp))
 For Each cell In MyRange
 
 If cell.Value = "_formatversion = 1.0" Then
 ActiveCell.Range (cell.Value)
 Selection.Insert Shift:=xlDown
 End If
 Next cell
End Sub

Display More

jolivanes · Apr 18th 2020

Try

Code

Sub Maybe()
Dim i As Long
For i = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Cells(i, 2).Value = "_formatversion = 1.0" Then Cells(i, 2).Resize(3).Insert Shift:=xlDown    '<---- _formatversion = 1.0 down also
'or
'        If Cells(i, 2).Value = "_formatversion = 1.0" Then Cells(i, 2).Offset(1).Resize(3).Insert Shift:=xlDown    '<---- _formatversion = 1.0 does not move
Next i
End Sub

When deleting, inserting etc, always from the bottom up.

sekar_r24 · Apr 18th 2020

Hi jolivanes

Perfect. Kudos.!

jolivanes · Apr 18th 2020

Glad it worked for you

Good luck and stay safe

Shifting a cell based on a string value in a column

Participate now!

Share