Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Monday, 19 March 2018
  0 Replies
  4.8K Visits
0
Votes
Undo
Hello I have in the past used VBA to do certain things in Office and have forgotten more than I remember. That being said, I am using Microsoft Windows 7 Home Premium with an older version of Microsoft Office Excel (am thinking 2003).

I have a spreadsheet I am using to track my media collection of mp4s and such. By using a dir command in command box to output a txt file that I parse to render cells a with a path to the media (mainly mp4s) and the actual filename.mp4. across the range A:I

What I am trying to accomplish is to move every row in a range that has a cell with a filename.mp4 to another sheet.
This is my code thus far:

Sub MoveMp4()
'Private Sub Worksheet_Change(ByVal Target As Range) [[use this as top line to run automatically if desired]]
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet1").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet1").Range("A:I") '[[RANGE CONTAINING FILENAME.MP4 CHANGES DYNAMICALLY]]
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = ".mp4" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = ".mp4" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub

Again, the above code works if a single cell in range A:I contains .mp4, yet will not move the row if there are characters before the .mp4 (as most filenames) ExcelVBAExample2.jpg
Perhaps it needs search the cells in the dynamically changing range for the .mp4 extension and when found move entire row to Sheet 2. I know i used to use the a command to search a string tor certain characters and operate on them accordingly, yet I cannot remember which or how I did.

Apologies for the length of the post yet I wanted to be as clear as possible as to what I desire as opposed to going back and forth with explanations. I have enclosed an attachment of an example of what I am working with.

I should like to say that I adapted the code from a post here:

https://www.extendoffice.com/documents/excel/3723-excel-move-row-to-another-sheet-based-on-cell-value.html?page_comment=1

And finally I should like to add that, you people are doing a great service to the community of the world, Thank You from the bottom of my heart soul and mind!
There are no replies made for this post yet.