Shift Excel Row Based on Cell Value

lilhill

New Member
Joined
Mar 23, 2009
Messages
5
Hi

I have an excel spreadsheet with two columns of data. The first column is all descriptors. The second column is data that pertains to the descriptors in the first column.

I need a simple VBS script which will read through the document and if a cell in column A contains the text "file name:" (exactly that with the ":" if possible), then a row is inserted ABOVE the row with the text "file name:", thus shifting all other data down one row.

The reason it has to be exactly "file name:" is because other rows contain "file name (with full path)". Not sure if it's an issue at all.

Thanks for the help!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hello and welcome

try

Code:
Sub INSERT_ROW()
    For MY_ROWS = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        If Range("A" & MY_ROWS).Value = "file name:" Then
            Rows(MY_ROWS).Insert
        End If
    Next MY_ROWS
End Sub
 
Upvote 0
Thanks for the code, but it didn't do anything. I'm going to try and tweak it to see if it can get it to run...
 
Upvote 0
I was not able to get the script to run correct in excel. It still appears to do nothing. Any ideas?
 
Upvote 0
Hello,

are the cells "file name:" exactly that, or are there any leading/trailing spaces?
 
Upvote 0
try
Code:
Sub test()
Columns(1).Insert
With Range("b1", Range("b" & Rows.Count).End(xlUp)).Offset(, -1)
    .Formula = "=find(""file name:"",b2)"
    On Error Resume Next
    .SpecialCells(-4123, 1).EntireRow.Insert
End With
Columns(1).Delete
End Sub
 
Upvote 0
The cells are called "file name:" exactly. There are no other spaces or characters in it.

Seiya, your script works but only if I change the "b" to "a" for the column, and it inserts the "row" to the left of column "a" instead of above. I think I forgot to mention that the "file name:" cell is in column "a".

Thanks for helping guys!
 
Upvote 0
Success!

Sub Insert_Row()
'Find last row
lRow = Range("A" & Rows.Count).End(xlUp).Row
'Start at bottom and work up
For nxtRow = lRow To 1 Step -1
'Check for string
If Range("A" & nxtRow) = "File Name:" Then
'Insert Row if required
Range("A" & nxtRow).EntireRow.Insert
End If
'Loop
Next
End Sub

Thank you all for your efforts :)
 
Upvote 0

Forum statistics

Threads
1,203,223
Messages
6,054,228
Members
444,711
Latest member
Stupid Idiot

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