Katakana to hiragana VBA

[llorcs]

Hi all!

I have an excel file where I have to change Katakana characters to Hiragana. I've been using StrConv + vbHiragana for some time, however, it is really slow since I am using it in a range for a 1000 rows (two times). After searching around, I've found the following code, which seems like a different approach and looks really fast. My only problem is, that it only works if I select the specified row and column (e.g. from A1:A10) and then if I run it, it does the job perfectly. I need to tweak this so it automatically does it in a range and displays the results on a different sheet.　(From a1:A1000 + D1:D1000)
Like this:

Sheet1:

A	B	C	D
山田	ヤマダ	太郎	タロウ

<tbody>
</tbody>

Sheet2:

A	B	C	D
山田	やまだ	太郎	たろう

<tbody>
</tbody>

Here is the code I've found:

Sub Comm()
Dim i, gyos, retus, rwsu As Integer
Dim KATA, HIRA As String


gyos = ActiveCell.Row
retus = ActiveCell.Column
rwsu = Selection.Rows.Count - 1


For n = gyos To gyos + rwsu
   KATA = Cells(n, retus)
   HIRA = StrConv(KATA, 32)
   Cells(n, retus) = HIRA
Next n


End Sub

Thank you for the help!

 

[RickXL]

Hi,

I am not sure how I create cells with Katakana in them so I can't test this:

Sub Convert()
    Dim arr  As Variant
    Dim i    As Long
    Dim j    As Long
    
    arr = Worksheets("Sheet1").Range("A1:D1000")
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            arr(i, j) = StrConv(arr(i, j), 32)
        Next
    Next
    Worksheets("Sheet2").Range("A1:D1000") = arr
End Sub

It reads a fixed range from Sheet1, performs the conversion then writes it back to Sheet2.

You can change the worksheet names by overtyping.

Regards,