Run macro on next row

[capo4410]

Hello all,

I need the macro created below to preform same action in subsequent rows, same column. I though the range copy method would work, but I am missing something.

Sub RemoveHypens()

Dim the_string As String
the_string = Sheets("Gopher Items CSV Test").Range("E3")
the_string = Replace(the_string, "-", "")
Sheets("Gopher Items CSV Test").Range("E3") = the_string
End Sub


Sub Range_Copy()
Dim r
Set r = Selection.Cells(1).EntireRow.Range("E3") 'relative to row, not sheet
Sheets("Gopher Items CSV Test").Range("E").Copy r
End Sub

 

[igold]

I have a question:

How many cells in Column E do you want to remove the hyphens from. Starting at E3 and going down how many rows, all of them, or just a set amount, or you tell me.

 

[capo4410]

I have a question:

How many cells in Column E do you want to remove the hyphens from. Starting at E3 and going down how many rows, all of them, or just a set amount, or you tell me.

There will be many sheets created. Some would have 5 rows others could have a few hundred.

 

[igold]

You never mentioned anything about sheets being created, nonetheless, how is the code supposed to know whether it is 5 rows or a few hundred. What is the criteria.

 

[capo4410]

Not sheets, I meant files containing varying amounts of data. Sorry for the confusion. We will be creating a file a day. Some days the file may contain 5 rows of data, some days could be 100. After all data is entered on the sheet, we would like to run the ‘remove hyphen’ macro to remove hypens from data in coulmn E.

 

[igold]

See if this does what you want. Run it while the sheet you want to remove the hyphens from is the active sheet. This code will overwrite existing data that will not be you will not be able to recover, so please test on a backup copy of your data.

Sub RemoveHyphens()


    Dim arr
    Dim i As Long
    
    arr = Range("E3:E" & Cells(Rows.Count, 5).End(xlUp).Row)
    For i = LBound(arr) To UBound(arr)
        arr(i, 1) = Replace(arr(i, 1), "-", "")
    Next
    Range("E3").Resize(UBound(arr)) = arr
    
End Sub

I hope this helps...

 

[capo4410]

This works great! Thank you very much!

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback!