• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA applying function to range

T

Tripp

Member
Hello,

I have a function which removes "<>" characters from a string and I would like to apply this to all the UsedRange in a column.

The function works fine in the worksheet as =RemoveSpecialChar() but I cant get it to work as part of my VBA.

How can I apply this function to all the cells in the UsedRange but replace the contents of the original cell with the result?

Regards,

Tripp
 
Hi ,

See if this works.
Code: 
Public Sub CallUDF()
          Const SPECIFICCOLUMN = 2 'change this to suit ; 2 refers to column B
           Dim lastrow As Long
          Dim inputdatarange As Range , cell As Range
        
          With ActiveSheet
                lastrow = .Cells.SpecialCells(xlLastCell).Row
                Set inputdatarange = .Range(.Cells(1, SPECIFICCOLUMN), .Cells(lastrow, SPECIFICCOLUMN))
          End With
        
          For Each cell In inputdatarange
              cell.Value = RemoveSpecialChar(cell)
          Next
End Sub
Narayan
 
Thanks Narayan, It always looks so simple when you write it!

I am having an issue though with states the RemoveSpecialChar(CELL) is a byRef argument type mismatch as if it doesnt like "cell' being a range...?
 
Hi ,

Try using cell.Value instead of cell when calling RemoveSpecialChar , as follows :

cell.Value = RemoveSpecialChar(cell.Value)

If even this does not work , upload your workbook with the code for RemoveSpecialChar in it.

Narayan
 
Hi ,

See if this link helps :

http://www.i-programmer.info/ebooks/automating-excel/1714-getting-at-the-data.html?start=1

In brief , I can say that every reference to an object should be fully qualified.

When we refer to a worksheet cell using the Cells method , Excel assumes that you wish to refer to the active sheet.

Thus , if your active sheet is named Sheet3 , then both of the following will return the same result :

Cells(6,2).Value

Worksheets("Sheet3").Cells(6,2).Value

However , what if you wished to refer to the cell D17 in the sheet named Sheet7 , while your active sheet remained Sheet3 ?

You would have to fully qualify the reference by using :

Worksheets("Sheet7").Cells(17,4).Value

Now what if you wished to access the values in cells D17 , D18 , D19 and D20 in the sheet named Sheet7 ?

You would have to use fully qualified references for all of the 4 cells , as follows :

Worksheets("Sheet7").Cells(17,4).Value
Worksheets("Sheet7").Cells(18,4).Value
Worksheets("Sheet7").Cells(19,4).Value
Worksheets("Sheet7").Cells(20,4).Value

Instead of typing out these lengthy references , you can use the With ... End With statement as follows :
Code: 
With Worksheets("Sheet7")
     .Cells(17,4).Value
     .Cells(18,4).Value
     .Cells(19,4).Value
     .Cells(20,4).Value
End With
Within a With ... End With block , any reference which starts with a dot sign . , refers to an object whose fully qualified reference would include the reference used in the With line of code. A worksheet cell reference which did not start with a dot . sign , would continue to refer to the active sheet.

Narayan
 
Hi again,

So the code is working beautifully for small ranges but I just used it with 7000 rows and it was incredibly slow. Can you advise on improving efficiency?

I have also changed the lastrow method to use the table as SpecialCell wasn't updating properly

Thank you.

Code: 
Function RemoveSpecial(Str As String) As String 'Removes Special Characters from cell

    Dim xChars As String
    Dim I As Long
    xChars = "<>-"  'These are the characters that will be removed
    For I = 1 To Len(xChars)
        Str = Replace$(Str, Mid$(xChars, I, 1), "") ' Cycles through the xChar String to find any of the special charaters
    Next
    RemoveSpecial = Str
End Function

Public Sub ExportTidy()  ' removes characters from cells in SPECIFICCOLUMN

         
          Const ColNo = 5 'choose column number e.g. 5 is column E
          Dim lastrow As Long
          Dim inputdatarange As Range, cell As Range
         
          Sheets("Sheet1").Activate
         
          Application.ScreenUpdating = False
       
          With ActiveSheet
                lastrow = .ListObjects("Table1").Range.Rows.Count ' returns the last row in the table
                Set inputdatarange = .Range(.Cells(1, ColNo), .Cells(lastrow, ColNo))
          End With
               
          For Each cell In inputdatarange
              cell.Value = RemoveSpecial(cell.Value)
          Next
         
          Application.ScreenUpdating = True
End Sub
 
Hi ,

Can you say whether in these 7000 rows , every row cell has the special characters , or do only some cells have them ?

If so , rather than operate on every character in every cell , we can immediately exclude those cells which do not have any special character in them.

Narayan
 
Hi ,

The execution time for this was just 2 seconds.

Extrapolating this to 7000 rows would mean an execution time of around a minute or so.

Is this excessive ?

Narayan
 
7000 is one of the smallest sample size I have and it definitely took longer than a minute for 7000 on my machine. Many of my samples are over 20,000 some even towards 100,000. which would take about 13 minutes by your account.

Just curious if it can be made faster/ what methods you could use.
 
Narayan - Unfortunately not. there are non in this sample but it is a possibility for future samples.

Marc - Im not sure what you mean?
 
Hi ,

So we have to consider the possibility that a special character can occur anywhere within a cell contents ?

What about the possibility of more than one special character within a cell contents ?

Would it be possible for you to upload a workbook with the 7000 rows of data ?

Narayan
 
You must log in or register to reply here.
Back
Top